﻿'Description:批次检验类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yangsj
'create Date:2017-5-08
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Imports System.Configuration
Imports BusinessRule.Common

Public Class uLMSCheckBusiness
    Implements IBusiness.uLMSCheckIBusiness

    Private m_CheckPoint As New uLMSStepCheckPointBusiness
    Private m_ContainerObj As New uLMSContainerBusiness
    Private m_CommonObj As New uLMSCommonBusiness
    Private m_Commission As New uLMSCommissioningBusiness
    Private usingImg As String = ConfigurationManager.AppSettings("UsingCheckImageSavePath")
    ''' <summary>
    ''' 获取首件批次号
    ''' </summary>
    ''' <param name="contaienrId"></param>
    ''' <param name="stepId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFirstCheckContainerNo(ByVal contaienrId As String, ByVal stepId As String) As String Implements uLMSCheckIBusiness.GetFirstCheckContainerNo
        Dim strSql As String = " SELECT cswr.firstcheckcontainerno FROM containerstepworkreport cswr WHERE cswr.workflowstepid = '{0}' AND cswr.containerid = '{1}' AND cswr.isuse = 0"
        strSql = String.Format(strSql, stepId, contaienrId)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim strNotes As String = ""
        For Each dr As DataRow In dt.Rows
            strNotes += dr("firstcheckcontainerno").ToString + ","
        Next
        Return strNotes.Trim(",")
    End Function
    ''' <summary>
    ''' 获取待检验批次
    ''' Create：YangSJ
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Function GetContainerCheckData(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetContainerCheckData
        Dim strSelect As String =
            <string>
             SELECT NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.PLANNEDSTARTDATE,
             C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NVL(UA.ATTRIBUTEVALUE,C.PRODUCTDESCRIPTION) ZBJH,(nvl(C.workflowroute,C.productname)||'/'||c.containerno) ProductBatch,
            (C.productname||'/'||c.containerno) ProductBatchHidden,C.containerno,
            </string>
        Dim sb As New StringBuilder
        Dim strsb As New StringBuilder
        sb.AppendLine(strSelect)
        ' sb.AppendLine("NVL(C.WORKFLOWROUTE,C.PRODUCTNAME)||'/'||REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,''ISKEY,''SPECNAME,''SPECREVISION,''PARENTSPECNO")
        sb.AppendLine(",NULL NONCONFORMINGNUM,NULL OriginalFactoryID FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" LEFT JOIN USERATTRIBUTE UA ON UA.PARENTID = C.MFGORDERID AND UA.USERATTRIBUTENAME = '组别件号'")
        sb.AppendLine(" LEFT JOIN containerstepnoship csn ON csn.containerid=c.containerid AND csn.workflowstepid=c.workflowstepid AND csn.isuse=0 AND csn.noship=1 ")
        If QueryCondition.Keys.Contains("BGXZJY") Then 'add tianFW 2018年3月27日
            strsb.AppendLine("INNER JOIN(")
            strsb.AppendLine("SELECT CSW.CONTAINERID, CSW.WORKFLOWSTEPID")
            strsb.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
            strsb.AppendFormat(" WHERE (CSW.CHECKERID = '{0}' OR CSW.CHECKERID IS NULL)", QueryCondition("CheckerID"))
            strsb.AppendLine("AND CSW.ISUSE = 0")
            strsb.AppendLine("GROUP BY CSW.CONTAINERID, CSW.WORKFLOWSTEPID")
            strsb.AppendLine(") CKI ON CKI.CONTAINERID=C.CONTAINERID AND CKI.WORKFLOWSTEPID = C.WORKFLOWSTEPID")
            sb.AppendLine(strsb.ToString())
        End If
        Dim sbCondition As String = GetCondition(QueryCondition)
        sbCondition += " AND (C.FIRSTCHECKNUM=1 OR C.STEPCHECKNUM=1) AND C.SplitNum=0 AND C.CONTAINERSTATUS=1 " 'tianfw Del AND C.DISPATCHNUM=1 2017-07-11 2017/11/02 Add AND C.CONTAINERSTATUS=1

        sbCondition += String.Format(" AND (CASE WHEN C.Originalfactoryid='{0}' and csn.containerstepnoshipid is null THEN C.SPECFACTORYID ELSE '{0}' END)='{0}'", QueryCondition("FactoryID")) 'add:Wangjh 增加工序分厂判断，避免当前转工序是检验序，且本分厂误检情况
        sbCondition += String.Format(" AND (CASE WHEN C.Originalfactoryid<>'{0}' and csn.containerstepnoshipid is null THEN C.SHIPNUM ELSE 5 END)>4", QueryCondition("FactoryID")) '增加转工限制

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        If QueryCondition.Keys.Contains("ScanContainerName") Then 'add by tianFW 2018年6月8日
            sb.AppendFormat(" AND C.TOGETHERFLAG='{0}'", QueryCondition("ScanContainerName"))
        End If
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,")
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,C.WORKFLOWROUTE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,ua.attributevalue,c.containerno")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        '  sb.AppendLine("NVL(C.WORKFLOWROUTE,C.PRODUCTNAME)||'/'||REPLACE(C.CONTAINERNAME,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.FIRSTCHECKNUM,C.STEPCHECKNUM,C.ISKEY,C.SPECNAME,C.SPECREVISION,C.PARENTSPECNO")
        sb.AppendLine(",C.NONCONFORMINGNUM,C.OriginalFactoryID FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" LEFT JOIN USERATTRIBUTE UA ON UA.PARENTID = C.MFGORDERID AND UA.USERATTRIBUTENAME = '组别件号'")
        sb.AppendLine(" LEFT JOIN containerstepnoship csn ON csn.containerid=c.containerid AND csn.workflowstepid=c.workflowstepid AND csn.isuse=0 AND csn.noship=1 ")
        If QueryCondition.Keys.Contains("BGXZJY") Then 'add tianFW 2018年3月27日
            sb.AppendLine(strsb.ToString())
        End If
        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        If QueryCondition.Keys.Contains("ScanContainerName") Then 'add by tianFW 2018年6月8日
            sb.AppendFormat(" AND C.CONTAINERNAME='{0}'", QueryCondition("ScanContainerName"))
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        Dim containerNoSql As String = "SELECT DECODE(ccs.notes,NULL,'否','是') Ishandle,C.* FROM CONTAINERCURRENTINFO C" &
                                        " LEFT JOIN containerchecksuspending ccs ON ccs.containerid=c.containerid AND ccs.workflowstepid=c.workflowstepid AND ccs.isuse=0 " &
                                        " LEFT JOIN containerstepnoship csn ON csn.containerid=c.containerid AND csn.workflowstepid=c.workflowstepid AND csn.isuse=0 AND csn.noship=1 "
        If QueryCondition.Keys.Contains("BGXZJY") Then 'add tianFW 2018年3月27日
            sbCondition = String.Format(containerNoSql & " {0} {1}", strsb.ToString(), sbCondition)
        Else
            sbCondition = String.Format(containerNoSql & " {0}", sbCondition) '将视图更换为新表 tianfw2017-06-05
        End If
        ' Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        Dim sonSql As DataTable = New DataTable
        sonSql.Columns.Add("ChildContainerSql")
        sonSql.Columns("ChildContainerSql").DefaultValue = sbCondition
        sonSql.Rows.Add()
        sonSql.AcceptChanges()
        result.DBTable.Merge(sonSql)
        result.DBTable.Rows.RemoveAt(result.DBTable.Rows.Count - 1)
        result.DBTable.AcceptChanges()
        Return result
    End Function
    ''' <summary>
    ''' 查询单件单卡子卡的所需信息
    ''' add:Wangjh 20180817
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetChildContainerNo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetChildContainerNo
        para("ChildContainerSql") += String.Format(" and((C.CONTAINERTYPE=7 AND C.TOGETHERFLAG='{0}' AND C.PRODUCTID='{1}' AND C.WORKFLOWSTEPID='{2}' AND C.MFGORDERNAME='{3}') OR (C.CONTAINERTYPE<>7 AND C.CONTAINERNAME='{4}') )",
                                                   para("TogatherFlag"), para("ProductID"), para("WorkflowStepID"), para("MfgOrderName"), para("ProductBatchHidden"))
        para("ChildContainerSql") += " ORDER BY C.CONTAINERNAME"
        Return DatabaseHelper.Query_ReturnDatTable(para("ChildContainerSql"))
    End Function

    ''' <summary>
    ''' 创建批次查询数据条件
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE C.FACTORYID='{0}' ", para("FactoryID"))

        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:Wangjh 2017-8-8
            sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '{0}%'", para("MfgOrderName"))
        End If
        GetCondition = sbCondition.ToString
    End Function


    ''' <summary>
    ''' 获取首件检验单号
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="strFanctoryCode"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFirstCheckBillNo(ByVal strFanctoryCode As String) As String Implements uLMSCheckIBusiness.GetFirstCheckBillNo
        Dim sbCondition As New StringBuilder
        sbCondition.AppendLine("SELECT MAX(FIRSTCHECKBILLNO)  FROM CONTAINERCHECKINFO ")
        sbCondition.AppendFormat("  WHERE FIRSTCHECKBILLNO LIKE '{0}%' ", strFanctoryCode)
        Dim objValue As Object = DatabaseHelper.GetSingle(sbCondition.ToString)
        If objValue Is Nothing Then
            GetFirstCheckBillNo = String.Format("{0}-{1:D5}", strFanctoryCode, 1)
        Else
            Dim strBillNo As String = objValue.ToString.Replace(strFanctoryCode, "").Substring(1)
            GetFirstCheckBillNo = String.Format("{0}-{1:D5}", strFanctoryCode, Convert.ToInt32(strBillNo) + 1)
        End If

    End Function

    ''' <summary>
    ''' 添加批次检验记录V2.0
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddContainerCheckInfo(ByVal entity As ContainerCheckInfoDTO) As Boolean Implements IBusiness.uLMSCheckIBusiness.AddContainerCheckInfo

        Dim objPara As Object()
        objPara = FillContainerCheckInfo(entity)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(objPara.First, objPara.Last)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    Public Function FillContainerCheckInfo(ByVal entity As ContainerCheckInfoDTO) As Object() Implements IBusiness.uLMSCheckIBusiness.FillContainerCheckInfo

        Dim strsql As String =
            <string>
                    INSERT INTO CONTAINERCHECKINFO(CONTAINERID,FACTORYID,WORKFLOWSTEPID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,LOSSQTY,REWORKQTY,DEFECTQTY,
                    CONTAINERCHECKINFOID,CREATEDATE,CHECKNOTE,FIRSTCHECKBILLNO,CONTAINERSTEPWORKREPORTID,ISUSE,ISTRANSFER,CHECKTYPENOTE,FIRSTCHECKCONTAINERNO,AcceptanceQty2,UNQUALIFIEDFEATUREIDS)
                    VALUES(:containerid,:factoryid,:workflowstepid,:checkerid,:checkqty,:acceptanceqty,:lossqty,:reworkqty,:defectqty,
                    :containercheckinfoid,:createdate,:checknote,:firstcheckbillno,:containerstepworkreportid,0,0,:CHECKTYPENOTE,:FIRSTCHECKCONTAINERNO,:ACCEPTANCEQTY2,:UNQUALIFIEDFEATUREIDS)
                </string>
        Dim insertpar(17) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":checkqty", entity.CheckQty, OracleType.Number)
        insertpar(5) = DatabaseHelper.NewParameter(":acceptanceqty", entity.AcceptanceQty, OracleType.Number)
        insertpar(6) = DatabaseHelper.NewParameter(":lossqty", (entity.LossQty + entity.SpecimenLossQty), OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":reworkqty", entity.ReworkQty, OracleType.Number)
        insertpar(8) = DatabaseHelper.NewParameter(":defectqty", entity.DefectQty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":containercheckinfoid", entity.ContainerCheckInfoID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":createdate", DateTime.Now, OracleType.DateTime)
        insertpar(11) = DatabaseHelper.NewParameter(":checknote", entity.CheckNote, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":firstcheckbillno", entity.FirstCheckBillNo, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":CHECKTYPENOTE", entity.CheckTypeNote, OracleType.Number)
        insertpar(15) = DatabaseHelper.NewParameter(":FIRSTCHECKCONTAINERNO", entity.FirstCheckContainerNo, OracleType.VarChar)
        insertpar(16) = DatabaseHelper.NewParameter(":ACCEPTANCEQTY2", entity.AcceptanceQty2, OracleType.Number)
        insertpar(17) = DatabaseHelper.NewParameter(":UNQUALIFIEDFEATUREIDS", IIf(String.IsNullOrWhiteSpace(entity.UnqualifiedFeatureIds), "", entity.UnqualifiedFeatureIds), OracleType.VarChar)
        Return New Object() {strsql, insertpar}
    End Function

    ''' <summary>
    ''' 按唯一id删除批次检验记录V2.0
    ''' </summary>
    ''' <param name="containercheckinfoid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerCheckInfoBycciid(ByVal containercheckinfoid As String) As Boolean Implements IBusiness.uLMSCheckIBusiness.DelContainerCheckInfo
        Dim ulmCurrBuss_Obj As New uLMSContainercurrentinfoBusiness
        If ulmCurrBuss_Obj.UpdateStepOrFirstCheckNumWithDelCheckInfo(containercheckinfoid) Then
            '清除可能存在的检测项记录 add:Wangjh 20220421
            Dim strSql As String = ""
            strSql = <string>select str.stepcheckpointresultid from stepcheckpointresult str
left join containercheckinfo cci on cci.containerstepworkreportid=str.containerstepworkreportid
where cci.containercheckinfoid='{0}'</string>.Value

            Dim checkPointDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, containercheckinfoid))
            If checkPointDt.Rows.Count > 0 Then
                For Each checkPonitRow As DataRow In checkPointDt.Rows
                    m_CheckPoint.DeleteCheckResultById(checkPonitRow("stepcheckpointresultid").ToString, usingImg)
                Next
            End If

            Dim checkDt = DatabaseHelper.Query_ReturnDatTable(String.Format("select * from CONTAINERCHECKINFO C WHERE C.CONTAINERCHECKINFOID = '{0}'", containercheckinfoid))
            If checkDt.Rows.Count = 0 Then
                Return False
            End If

            '删除检验记录，删除数量变更记录，删除工时记录,删除报废单记录,删除推送erp记录
            Dim sqlList As New ArrayList
            sqlList.Add(String.Format("update CONTAINERCHECKINFO C set isuse=1 WHERE C.CONTAINERCHECKINFOID = '{0}'", containercheckinfoid))
            sqlList.Add(String.Format("update  containerstepscrapinfo C set isuse=1,IsIntegration=1 WHERE C.qtyhistorydetailsid in (select ch.checkqtychangerecordid from checkqtychangerecord ch where ch.containercheckinfoid='{0}')", containercheckinfoid))
            sqlList.Add(String.Format("update  Checkqtychangerecord C set isuse=1 WHERE C.CONTAINERCHECKINFOID = '{0}'", containercheckinfoid))
            sqlList.Add(String.Format("delete  containersteptimesheetsplit css where css.containersteptimesheetid in (select CONTAINERSTEPTIMESHEETID from CONTAINERSTEPTIMESHEET where CONTAINERCHECKINFOID='{0}') ", containercheckinfoid))
            sqlList.Add(String.Format("DELETE CONTAINERSTEPTIMESHEET CSS WHERE CSS.CONTAINERCHECKINFOID='{0}' ", containercheckinfoid))
            sqlList.Add("UPDATE Writetoerprecord wt SET wt.isuse=1 WHERE wt.recordid='" + checkDt.Rows(0)("containerid").ToString + checkDt.Rows(0)("workflowstepid").ToString + "'")
            sqlList.Add("UPDATE mes_workingreport_table wt SET wt.isuse=1,STATUS=0,reportdate=to_date(to_char(SYSDATE,'yyyy/MM/dd'),'yyyy/MM/dd') WHERE isuse=0 and wt.containerstep='" + checkDt.Rows(0)("containerid").ToString + checkDt.Rows(0)("workflowstepid").ToString + "'")
            sqlList.Add(String.Format("update containersteprecord csr set csr.usecompleted=0 where csr.containerid='{0}' and csr.workflowstepid='{1}' and csr.usecompleted=1", checkDt.Rows(0)("containerid").ToString, checkDt.Rows(0)("workflowstepid").ToString))

            DatabaseHelper.ExecuteSqlTran(sqlList)

            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 添加批次成品检验记录V2.0
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddContainerFinishedCheckInfo(ByVal entity As ContainerFinishedCheckInfoDTO) As Boolean Implements IBusiness.uLMSCheckIBusiness.AddContainerFinishedCheckInfo
        Dim strsql As String =
            <string>
            INSERT INTO CONTAINERFINISHEDCHECKINFO(CONTAINERID,FACTORYID,WORKFLOWSTEPID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,ACCEPTANCEQTY2,LOSSQTY,REWORKQTY,
            DEFECTQTY,BATCHNO,DEFECTBILLNO,CHECKRESULT,OTHERQUALITYRESULT,CHECKNOTE,TECHNIQUECONDITION,CONTAINERFINISHEDCHECKINFOID,CREATEDATE,ISUSE,ISTRANSFER,CheckTypeNote)
            VALUES(:Containerid,:Factoryid,:Workflowstepid,:Checkerid,:Checkqty,:Acceptanceqty,:ACCEPTANCEQTY2,:Lossqty,:Reworkqty,
            :Defectqty,:Batchno,:Defectbillno,:Checkresult,:Otherqualityresult,:Checknote,:Techniquecondition,:Containerfinishedcheckinfoid,Sysdate,0,0,:CheckTypeNote)
            </string>
        Dim insertpar(17) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":Checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":Checkqty", entity.CheckQty, OracleType.Number)
        insertpar(5) = DatabaseHelper.NewParameter(":Acceptanceqty", entity.AcceptanceQty, OracleType.Number)
        insertpar(6) = DatabaseHelper.NewParameter(":Lossqty", entity.LossQty, OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":Reworkqty", entity.ReworkQty, OracleType.Number)
        insertpar(8) = DatabaseHelper.NewParameter(":Defectqty", entity.DefectQty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":Batchno", entity.BatchNO, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":Defectbillno", entity.DefectBillNO, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":Checkresult", entity.CheckResult, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":Otherqualityresult", entity.OtherQualityResult, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":Checknote", entity.CheckNote, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":Techniquecondition", entity.TechniqueCondition, OracleType.VarChar)
        insertpar(15) = DatabaseHelper.NewParameter(":Containerfinishedcheckinfoid", entity.ContainerFinishedCheckInfoID, OracleType.VarChar)
        insertpar(16) = DatabaseHelper.NewParameter(":CheckTypeNote", entity.CheckTypeNote, OracleType.VarChar)
        insertpar(17) = DatabaseHelper.NewParameter(":ACCEPTANCEQTY2", entity.AcceptanceQty2, OracleType.Number)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function


    ''' <summary>
    ''' 按唯一ID删除批次成品检验记录V2.0
    ''' </summary>
    ''' <param name="ContainerFinishedCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerFinishedCheckInfoBycfciid(ByVal ContainerFinishedCheckInfoID As String) As Boolean Implements IBusiness.uLMSCheckIBusiness.DelContainerFinishedCheckInfoBycfciid
        Dim ulmCurrBuss_Obj As New uLMSContainercurrentinfoBusiness
        If ulmCurrBuss_Obj.UpdateStepOrFirstCheckNumWithDelFinishCheckInfo(ContainerFinishedCheckInfoID) Then
            '清除可能存在的检测项记录 add:Wangjh 20220421
            Dim strSql As String = ""
            strSql = <string>select str.stepcheckpointresultid from stepcheckpointresult str
left join containerstepworkreport csw on csw.containerstepworkreportid=str.containerstepworkreportid
where csw.containerfinishedcheckinfoid='{0}'</string>.Value

            Dim checkPointDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, ContainerFinishedCheckInfoID))
            If checkPointDt.Rows.Count > 0 Then
                For Each checkPonitRow As DataRow In checkPointDt.Rows
                    m_CheckPoint.DeleteCheckResultById(checkPonitRow("stepcheckpointresultid").ToString, usingImg)
                Next
            End If

            Dim checkDt = DatabaseHelper.Query_ReturnDatTable(String.Format("select * from CONTAINERFINISHEDCHECKINFO C WHERE C.CONTAINERFINISHEDCHECKINFOID = '{0}'", ContainerFinishedCheckInfoID))
            If checkDt.Rows.Count = 0 Then
                Return False
            End If

            '删除成检记录，删除数量变更记录，删除工时记录,删除报废单记录，删除推送erp记录
            Dim sqlList As New ArrayList
            sqlList.Add(String.Format("update CONTAINERFINISHEDCHECKINFO C set isuse=1 WHERE C.CONTAINERFINISHEDCHECKINFOID = '{0}'", ContainerFinishedCheckInfoID))
            sqlList.Add(String.Format("DELETE CONTAINERFINISHEDCHECKDETAIL C WHERE C.CONTAINERFINISHEDCHECKINFOID = '{0}'", ContainerFinishedCheckInfoID))
            sqlList.Add(String.Format("update  containerstepscrapinfo C set isuse=1,IsIntegration=1 WHERE C.qtyhistorydetailsid in (select ch.checkqtychangerecordid from checkqtychangerecord ch where ch.containercheckinfoid='{0}')", ContainerFinishedCheckInfoID))
            sqlList.Add(String.Format("update Checkqtychangerecord C set isuse=1 WHERE C.CONTAINERCHECKINFOID = '{0}'", ContainerFinishedCheckInfoID))
            sqlList.Add(String.Format("delete  containersteptimesheetsplit css where css.containersteptimesheetid in (select CONTAINERSTEPTIMESHEETID from CONTAINERSTEPTIMESHEET where CONTAINERCHECKINFOID='{0}') ", ContainerFinishedCheckInfoID))
            sqlList.Add(String.Format("DELETE CONTAINERSTEPTIMESHEET CSS WHERE CSS.CONTAINERCHECKINFOID='{0}' ", ContainerFinishedCheckInfoID))
            sqlList.Add("UPDATE Writetoerprecord wt SET wt.isuse=1 WHERE wt.recordid='" + checkDt.Rows(0)("containerid").ToString + checkDt.Rows(0)("workflowstepid").ToString + "'")
            sqlList.Add("UPDATE mes_workingreport_table wt SET wt.isuse=1,STATUS=0,reportdate=to_date(to_char(SYSDATE,'yyyy/MM/dd'),'yyyy/MM/dd') WHERE isuse=0 and wt.containerstep='" + checkDt.Rows(0)("containerid").ToString + checkDt.Rows(0)("workflowstepid").ToString + "'")
            sqlList.Add(String.Format("update containersteprecord csr set csr.usecompleted=0 where csr.containerid='{0}' and csr.workflowstepid='{1}' and csr.usecompleted=1", checkDt.Rows(0)("containerid").ToString, checkDt.Rows(0)("workflowstepid").ToString))

            DatabaseHelper.ExecuteSqlTran(sqlList)

            Return True

        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 添加批次成品检验记录明细V2.0
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddContainerFinishedCheckDetailInfo(ByVal entity As ContainerFinishedCheckDetailDTO) As Boolean Implements IBusiness.uLMSCheckIBusiness.AddContainerFinishedCheckDetailInfo
        Dim strsql As String =
           <string>
                INSERT INTO CONTAINERFINISHEDCHECKDETAIL(CHECKDETAILNAME,CHECKDETAILVALUE,CHILDCONTAINERID,CONTAINERFINISHEDCHECKINFOID,CREATEDATE,EMPLOYEEID,ISUSE)
                VALUES(:CheckDetailName,:CheckDetailValue,:ChildContainerID,:ContainerFinishedCheckInfoID,SYSDATE,:EmployeeID,0)
            </string>
        Dim insertpar(4) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":CheckDetailName", entity.CheckDetailName, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":CheckDetailValue", entity.CheckDetailValue, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":ChildContainerID", entity.ChildContainerID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":ContainerFinishedCheckInfoID", entity.ContainerFinishedCheckInfoID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":EmployeeID", entity.EmployeeID, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 按唯一ID删除批次成品检验记录明细V2.0
    ''' </summary>
    ''' <param name="ContainerFinishedCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerFinishedCheckDetailByccid(ByVal ContainerFinishedCheckInfoID As String) As Boolean Implements IBusiness.uLMSCheckIBusiness.DelContainerFinishedCheckDetailByccid
        Dim strsql As String = String.Format("UPDATE CONTAINERFINISHEDCHECKDETAIL C SET C.ISUSE=1 WHERE C.CONTAINERFINISHEDCHECKINFOID = '{0}'", ContainerFinishedCheckInfoID)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 工序是否工艺最后序
    ''' </summary>
    ''' <param name="strStepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsLastStepFinished(ByVal strStepID As String) As Boolean Implements uLMSCheckIBusiness.IsLastStepFinished
        IsLastStepFinished = False
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT WF.DEFAULTPATHID, WF.ISLASTSTEP, WF.WORKFLOWSTEPID, P.PATHID")
        sb.AppendLine("FROM WORKFLOWSTEP WF")
        sb.AppendLine("LEFT JOIN PATH P    ON P.TOSTEPID = WF.WORKFLOWSTEPID")
        sb.AppendFormat("WHERE WF.WORKFLOWSTEPID = '{0}' ", strStepID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            Return False
        End If
        Dim dr As DataRow = dt.Rows(0)
        If dr.IsNull("PATHID") Then '没有移动路径
            Exit Function
        End If
        ''表示最后一序或该序没有后续加工工序
        If dr("ISLASTSTEP") = "1" Or dr.IsNull("DEFAULTPATHID") Then
            IsLastStepFinished = True
        End If

    End Function
    ''' <summary>
    ''' 查询批次工艺的父工序是否是最后一道序
    ''' create:Wangjh 20171219
    ''' </summary>
    ''' <param name="strStepID"></param>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsLastFinishedOfFaWorkflow(ByVal strStepID As String, ByVal containerID As String) As DataTable Implements uLMSCheckIBusiness.IsLastFinishedOfFaWorkflow
        Dim strSql As String = <string>
                                SELECT (CASE WHEN ws.islaststep=1 OR WS.DEFAULTPATHID IS NULL THEN 1 ELSE 0  END) FAislaststep FROM  WORKFLOWSTEP WS--父工序
                                LEFT JOIN  SUBWORKFLOWCHECKINFO SWC  ON SWC.WORKFLOWID=WS.WORKFLOWID--父工艺关联子工艺校对表
                                LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=WS.SUBWORKFLOWBASEID
                                LEFT JOIN WORKFLOW W ON  W.WORKFLOWID=NVL(WB.REVOFRCDID,WS.SUBWORKFLOWID)--子工艺
                                LEFT JOIN Workflowstep subws ON subws.workflowid=w.workflowid--子工序
                               </string>
        strSql += String.Format(" WHERE subws.workflowstepid='{0}' AND swc.containerid='{1}'", strStepID, containerID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        IsLastFinishedOfFaWorkflow = dt
    End Function

    ''' <summary>
    ''' 获取批次的所有工序检和首检检验的合格数
    ''' creat:Wangjh 2017-6-14
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllAcceptQty(ByVal containerID As String) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetAllAcceptQty
        Dim strSql As String = String.Format("  SELECT CCI.ACCEPTANCEQTY,C.QTY  FROM CONTAINERCHECKINFO CCI" &
                                             " LEFT JOIN CONTAINER C ON C.CONTAINERID=CCI.CONTAINERID" &
                                             " LEFT JOIN CURRENTSTATUS CU ON CU.CURRENTSTATUSID=C.CURRENTSTATUSID" &
                                             " WHERE CCI.CONTAINERID='{0}' AND CCI.WORKFLOWSTEPID=CU.WORKFLOWSTEPID AND CCI.ISUSE=0", containerID)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetAllAcceptQty = dt

    End Function

    ''' <summary>
    ''' 获取批次的所有成品检验的合格数
    ''' creat:Wangjh 2017-6-14
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllFinishAcceptQty(ByVal containerID As String) As DataTable Implements uLMSCheckIBusiness.GetAllFinishAcceptQty
        Dim strSql As String = String.Format("  SELECT CCI.ACCEPTANCEQTY,C.QTY  FROM containerfinishedcheckinfo CCI" &
                                             " LEFT JOIN CONTAINER C ON C.CONTAINERID=CCI.CONTAINERID" &
                                             " 	LEFT JOIN CURRENTSTATUS CU ON CU.CURRENTSTATUSID=C.CURRENTSTATUSID" &
                                             " WHERE CCI.CONTAINERID='{0}' AND CCI.WORKFLOWSTEPID=CU.WORKFLOWSTEPID AND CCI.ISUSE=0", containerID)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetAllFinishAcceptQty = dt
    End Function
    ''' <summary>
    ''' 判断指定工序是否是该批次转工的最后一道工序
    ''' creat:Wangjh 2017-6-17
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsLastShipStep(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSCheckIBusiness.IsLastShipStep
        Dim strSql As String = <string>SELECT csr.TOWORKFLOWSTEPID,ws.sequence tosequence FROM containercurrentinfo cci
LEFT JOIN containershiprecord csr ON csr.containershiprecordid=cci.shipid
left join workflowstep ws on ws.workflowstepid=csr.TOWORKFLOWSTEPID
left join containershiprecive csre on csre.containershiprecordid=csr.containershiprecordid and csre.isuse=0    
        </string>.Value
        'strSql += String.Format(" WHERE CSR.TOFACTORYID = '{0}' AND cci.CONTAINERID = '{1}' AND CSR.TOWORKFLOWSTEPID = '{2}'", para("ToFactoryID"), para("ContainerID"), para("WorkFlowStepID"))
        strSql += String.Format(" WHERE CSR.TOFACTORYID = '{0}' AND cci.CONTAINERID = '{1}' and csre.ReciveDate is null ", para("ToFactoryID"), para("ContainerID"))


        'strSql += " UNION" '连接子工艺情况
        'strSql += " SELECT CSR.CONTAINERSHIPRECORDID FROM CONTAINERSHIPRECORD CSR"
        'strSql += " LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID=CSR.TOWORKFLOWSTEPID" '转工工序
        'strSql += " LEFT JOIN WORKFLOWBASE WB ON WB.WORKFLOWBASEID=WS.SUBWORKFLOWBASEID"
        'strSql += " LEFT JOIN WORKFLOW W ON W.WORKFLOWID=NVL(WB.REVOFRCDID,WS.SUBWORKFLOWID)" '子工艺
        'strSql += " LEFT JOIN WORKFLOWSTEP WS2 ON WS2.WORKFLOWID=W.WORKFLOWID" '当前工序
        'strSql += String.Format(" WHERE CSR.TOFACTORYID = '{0}' AND CSR.CONTAINERID = '{1}' AND WS2.WORKFLOWSTEPID = '{2}' AND (ws2.islaststep=1 OR ws2.defaultpathid IS NULL)", para("ToFactoryID"), para("ContainerID"), para("WorkFlowStepID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count > 0 Then
            If dt.Rows(0)("TOWORKFLOWSTEPID") = para("WorkFlowStepID") Then
                IsLastShipStep = True
            Else '转工，需要移动的情况
                para.Add("Out_ShipLastSequence", dt.Rows(0)("tosequence").ToString)
                para.Add("Out_ShipLastStepID", dt.Rows(0)("TOWORKFLOWSTEPID").ToString)
                IsLastShipStep = False
            End If
        Else
            IsLastShipStep = False
        End If
    End Function

    ''' <summary>
    ''' 获取批次当前工序的不合格审理信息
    ''' creat:Wangjh 2017-6-19
    ''' modify  CCI.CONTAINERSTATUS=1 2017年12月21日 添加检验员
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerNonconformingInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetContainerNonconformingInfo
        Dim strSql As New StringBuilder
        If para.Keys.Contains("checkemployee") Then
            strSql.AppendLine("SELECT CNI.*,EM.FULLNAME CHECKERNAME ")
        Else
            strSql.AppendLine("SELECT CCI.CONTAINERNAME,CNI.*,cni.createdate TXNDATE")
        End If
        strSql.AppendLine("FROM CONTAINERNONCONFORMINGINFO CNI ")
        strSql.AppendLine("INNER JOIN CONTAINERCURRENTINFO CCI ON CNI.CONTAINERID=CCI.CONTAINERID")
        If para.Keys.Contains("checkemployee") Then
            strSql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID=CNI.CHECKEMPLOYEEID")
        End If
        strSql.AppendLine("WHERE CCI.CONTAINERSTATUS=1 AND CNI.ISUSE=0 ")
        strSql.AppendFormat("AND CNI.CONTAINERID IN ({0}) AND CNI.WORKFLOWSTEPID = '{1}'", para("ContainerIDs"), para("WorkFlowStepID"))
        GetContainerNonconformingInfo = DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 更新报工表的报废数或成品检验ID
    ''' create:Wangjh 
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerStepWorkReport(ByVal para As Dictionary(Of String, Object)) As Boolean Implements uLMSCheckIBusiness.UpdateContainerStepWorkReport
        Dim objPara As Object()
        objPara = FillUpdateContainerStepWorkReport(para)
        Dim i As Integer = DatabaseHelper.ExecuteSql(objPara.First, objPara.Last)
        If i > 0 Then
            UpdateContainerStepWorkReport = True
        Else
            UpdateContainerStepWorkReport = False
        End If
    End Function

    Public Function FillUpdateContainerStepWorkReport(ByVal para As Dictionary(Of String, Object)) As Object() Implements uLMSCheckIBusiness.FillUpdateContainerStepWorkReport
        Dim updateSql As String = " UPDATE containerstepworkreport csw SET checktype=checktype "
        If para.ContainsKey("ContainerFinishedCheckInfoID") Then
            updateSql += String.Format(" ,containerfinishedcheckinfoid ='{0}' ", para("ContainerFinishedCheckInfoID"))
        End If
        If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
            updateSql += String.Format(" ,FactoryID ='{0}' ", para("FactoryID"))
        End If
        If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
            updateSql += String.Format(" ,workerid ='{0}' ", para("WorkerID"))
        End If
        If para.ContainsKey("LossQty") Then '更新报废数
            updateSql += String.Format(" ,LossQty ={0} ", para("LossQty"))
        End If
        updateSql += " WHERE csw.containerstepworkreportid=:containerstepworkreportid"
        If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
            updateSql += " AND csw.FactoryID is null"
        End If
        If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
            updateSql += " AND csw.workerid is null"
        End If

        Dim updatePara(0) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":containerstepworkreportid", para("ContainerStepWorkReportID"), OracleType.VarChar)
        Return New Object() {updateSql, updatePara}
    End Function

    ''' <summary>
    ''' 获取试验件报工
    ''' add:Wangjh 20180104
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>add AND csw.isuse = 0  by tianFW 2018年9月29日</remarks>
    Public Function GetTestReport(ByVal para As Dictionary(Of String, String)) Implements uLMSCheckIBusiness.GetTestReport
        Dim strSql As String = <string>
                                SELECT csw.* FROM containerstepworkreport csw
                                LEFT JOIN workflowstep ws ON ws.workflowstepid=csw.workflowstepid
                                where csw.checktype=3 AND csw.isuse = 0 
                               </string>
        If para.ContainsKey("ContainerID") Then
            strSql += String.Format(" and csw.containerid='{0}'", para("ContainerID"))
        End If
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" and WS.sequence={0}", para("Sequence"))
        End If
        GetTestReport = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取批次所有成品检验的批次 create tianFW 2017.07.19
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainer_CheckInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSCheckIBusiness.GetContainer_CheckInfo
        Dim strSelect As String =
            <string>
                SELECT CC.FACTORYID,PB.PRODUCTNAME,P.DESCRIPTION PRODUCTDESCRIPTION,C_.TOGETHERFLAG,P.PRODUCTID,C_.CONTAINERTYPE,C_.PLANNEDCOMPLETIONDATE,C_.PLANNEDSTARTDATE,
                DECODE(C_.CONTAINERTYPE,2,'批量加工',3,'批量带子序号',7,'单件加工') AS CONTAINERTYPENAME,MF.MFGORDERNAME,WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME
            </string>
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * FROM(")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",SUM(C_.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID ")
        Dim selectStr As String =
            <string>
                FROM CONTAINER C_ 
                LEFT JOIN PRODUCT P ON P.PRODUCTID = C_.PRODUCTID
	            LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID
	            LEFT JOIN MFGORDER MF ON MF.MFGORDERID = C_.MFGORDERID
	            LEFT JOIN CONTAINERFINISHEDCHECKINFO CC ON CC.CONTAINERID = C_.CONTAINERID
	            LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CC.WORKFLOWSTEPID
            </string>
        strsql.AppendLine(selectStr)
        Dim strcondition As New StringBuilder
        strcondition.AppendFormat("WHERE CC.CONTAINERFINISHEDCHECKINFOID IS NOT NULL AND C_.STATUS=1 AND CC.FACTORYID='{0}'", para("FactoryID"))
        If (para.Keys.Contains("ScanContainerName")) Then
            strcondition.AppendFormat(" AND C_.CONTAINERNAME='{0}'", para("ScanContainerName"))
        Else
            If (para.Keys.Contains("ContainerName")) Then
                strcondition.AppendFormat(" AND C_.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
            End If
            If (para.Keys.Contains("ProductName")) Then
                strcondition.AppendFormat(" AND PB.PRODUCTNAME LIKE '%{0}%'", para("ProductName"))
            End If
            If para.Keys.Contains("mfgOrderName") Then
                strcondition.AppendFormat(" AND MF.MFGORDERNAME  LIKE '%{0}%'", para("mfgOrderName"))
            End If
        End If
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE='7'")
        strsql.AppendLine(" GROUP BY CC.FACTORYID,PB.PRODUCTNAME,P.DESCRIPTION,C_.TOGETHERFLAG,P.PRODUCTID,C_.CONTAINERTYPE,C_.PLANNEDCOMPLETIONDATE,C_.PLANNEDSTARTDATE,")
        strsql.AppendLine("MF.MFGORDERNAME,WS.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME")
        strsql.AppendLine(" UNION ")
        strsql.AppendLine(strSelect)
        strsql.AppendLine(",C_.QTY AS WIPQTY , C_.CONTAINERID,C_.CONTAINERNAME,C_.CONTAINERID AS FaContianerID")
        strsql.AppendLine(selectStr)
        strsql.AppendLine(strcondition.ToString())
        strsql.AppendLine("AND C_.CONTAINERTYPE<>'7'")
        strsql.AppendLine(") C ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim strValue As String = ""
        strValue += "SELECT * FROM("
        strValue += strSelect
        strValue += ",C_.QTY,C_.CONTAINERID,C_.CONTAINERNAME"
        strValue += selectStr
        strValue += strcondition.ToString()
        strValue += ") C WHERE 1=1 "
        Return DatabaseHelper.GetContainerForPage(paraPage, strValue)
    End Function


    ''' <summary>
    ''' 获取成品检验信息 create tianFW 2017.07.19
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerFinishCheckInfo(ByVal ContainerID As String, ByVal WorkflowStepID As String) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetContainerFinishCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT PR.PRODUCTNAME, P.DESCRIPTION,CFC.CONTAINERFINISHEDCHECKINFOID,C.CONTAINERNAME,WS.WORKFLOWSTEPNAME, ")
        strsql.AppendLine("CFC.BATCHNO,C.CONTAINERID,CFC.REWORKQTY,CFC.DEFECTBILLNO,CFC.CHECKRESULT,CFC.TECHNIQUECONDITION,CFC.OTHERQUALITYRESULT,")
        strsql.AppendLine("CFC.CREATEDATE,CFC.CHECKNOTE,CFC.CHECKQTY,CFC.ACCEPTANCEQTY,CFC.DEFECTQTY,EM.FULLNAME CHECKERNAME,CFC.LOSSQTY ")
        strsql.AppendLine("FROM CONTAINER C")
        strsql.AppendLine("LEFT JOIN CONTAINERFINISHEDCHECKINFO CFC ON C.CONTAINERID = CFC.CONTAINERID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CFC.CHECKERID ")
        strsql.AppendLine("LEFT JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID ")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PR ON PR.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CFC.WORKFLOWSTEPID")
        strsql.AppendLine("WHERE C.STATUS=1 AND CFC.ISUSE=0 AND CFC.CONTAINERFINISHEDCHECKINFOID IS NOT NULL")
        strsql.AppendFormat(" AND ({0}) AND CFC.WORKFLOWSTEPID='{1}' ORDER BY CFC.CREATEDATE DESC", ContainerID, WorkflowStepID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 根据成品检验ID获取成检详细信息 create tianFW 2017/07/21
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFinishCheckInfoDetail(ByVal containerFinishedCheckInfoIDs As String) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetFinishCheckInfoDetail
        Dim strsql As String = "SELECT C.CHECKDETAILNAME, C.CHECKDETAILVALUE, EM.EMPLOYEENAME,EM.FULLNAME,C.CONTAINERFINISHEDCHECKINFOID,C.EMPLOYEEID " &
                               " FROM CONTAINERFINISHEDCHECKDETAIL C LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = C.EMPLOYEEID "
        strsql += String.Format(" WHERE {0}", containerFinishedCheckInfoIDs)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 获取所有的检验记录 create tianFW 2017/08/17
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHistoryCheckInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetHistoryCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CC.CREATEDATE AS TXNDATE, EM.FULLNAME AS CHECKERNAME, CC.ACCEPTANCEQTY,CC.CONTAINERSTEPWORKREPORTID,")
        strsql.AppendLine("NVL(CC.LOSSQTY,0) LOSSQTY, CC.CONTAINERID, CC.WORKFLOWSTEPID,CC.CHECKQTY,CC.CONTAINERCHECKINFOID,'' CONTAINERNAME,")
        strsql.AppendLine("kcqcr.containercheckinfoid cqcrid,nvl(kcqcr.cqcrqty,0) cqcrqty")
        strsql.AppendLine("FROM CONTAINERCHECKINFO CC")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CC.CHECKERID")
        strsql.AppendLine("LEFT JOIN (SELECT cqcr.containercheckinfoid, SUM(cqcr.qty) cqcrqty")
        strsql.AppendLine("FROM checkqtychangerecord cqcr")
        strsql.AppendLine("WHERE cqcr.isuse = 0")
        strsql.AppendLine("GROUP BY cqcr.containercheckinfoid) kcqcr ON kcqcr.containercheckinfoid = cc.containercheckinfoid")
        If para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat(" WHERE CC.CONTAINERID IN ({0}) AND CC.WORKFLOWSTEPID = '{1}' AND CC.ISUSE=0 ORDER BY CC.CREATEDATE DESC", para("ContainerIDs"), para("WorkflowStepID"))
        Else
            strsql.AppendFormat(" WHERE CC.CONTAINERID = '{0}' AND CC.WORKFLOWSTEPID = '{1}' AND CC.ISUSE=0 ORDER BY CC.CREATEDATE DESC", para("ContainerID"), para("WorkflowStepID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取所有的成检记录 create tianFW 2017/08/17
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetHistoryFinishCheckInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetHistoryFinishCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CC.CREATEDATE AS TXNDATE, EM.FULLNAME AS CHECKERNAME, CC.ACCEPTANCEQTY,'' CONTAINERNAME,")
        strsql.AppendLine("NVL(CC.LOSSQTY,0) LOSSQTY, CC.CONTAINERID, CC.WORKFLOWSTEPID,CC.CHECKQTY,CC.CONTAINERFINISHEDCHECKINFOID,")
        strsql.AppendLine("kcqcr.containercheckinfoid cqcrid,nvl(kcqcr.cqcrqty,0) cqcrqty")
        strsql.AppendLine("FROM CONTAINERFINISHEDCHECKINFO CC")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CC.CHECKERID")
        strsql.AppendLine("LEFT JOIN (SELECT cqcr.containercheckinfoid, SUM(cqcr.qty) cqcrqty")
        strsql.AppendLine("FROM checkqtychangerecord cqcr")
        strsql.AppendLine("WHERE cqcr.isuse = 0")
        strsql.AppendLine("GROUP BY cqcr.containercheckinfoid) kcqcr ON kcqcr.containercheckinfoid = cc.CONTAINERFINISHEDCHECKINFOID")
        If para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat(" WHERE CC.CONTAINERID IN ({0}) AND CC.WORKFLOWSTEPID = '{1}' AND CC.ISUSE=0 ORDER BY CC.CREATEDATE DESC", para("ContainerIDs"), para("WorkflowStepID"))
        Else
            strsql.AppendFormat(" WHERE CC.CONTAINERID = '{0}' AND CC.WORKFLOWSTEPID = '{1}' AND CC.ISUSE=0 ORDER BY CC.CREATEDATE DESC", para("ContainerID"), para("WorkflowStepID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取成检继承信息 create tianFW 2018年1月11日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetLastFinishCheckInheritInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetLastFinishCheckInheritInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * FROM CONTAINERFINISHEDCHECKINFO CIO")
        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CIO.WORKFLOWSTEPID")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CIO.CONTAINERID")
        strsql.AppendLine("LEFT JOIN PRODUCT P ON P.PRODUCTID = C.PRODUCTID")
        strsql.AppendLine("LEFT JOIN PRODUCTBASE PB ON PB.PRODUCTBASEID = P.PRODUCTBASEID")
        strsql.AppendFormat(" WHERE PB.PRODUCTNAME='{0}' AND WS.WORKFLOWSTEPNAME='{1}' AND CIO.ISUSE=0 ", para("ProductName"), para("WorkflowStepName"))
        strsql.AppendLine("ORDER BY CIO.CREATEDATE DESC")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 检验前的验证
    ''' add:Wangjh 20180524
    ''' </summary>
    ''' <param name="workReportID"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ContainerCheckValidate(ByVal workReportID As String, ByVal type As Integer) As Boolean Implements uLMSCheckIBusiness.ContainerCheckValidate
        Dim strSql As String = ""
        If type = 0 Then '验证工序检
            strSql = <string> 
                    SELECT COUNT(cci.containercheckinfoid) checknum FROM containercheckinfo cci
                    WHERE cci.isuse=0 
                </string>
            strSql += String.Format(" AND cci.containerstepworkreportid='{0}'", workReportID)
        End If

        If type = 1 Then '验证成检
            'add AND csw.isuse = 0  by tianFW 2018年9月29日
            strSql = <string>
                        SELECT COUNT(csw.containerfinishedcheckinfoid) checknum FROM containerstepworkreport csw
                        WHERE csw.containerfinishedcheckinfoid  IS NOT NULL AND csw.isuse = 0 
                    </string>
            strSql += String.Format(" AND csw.containerstepworkreportid='{0}'", workReportID)
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows(0)("checknum") > 0 Then
            ContainerCheckValidate = False '已有记录
        Else
            ContainerCheckValidate = True '无记录
        End If
    End Function

    ''' <summary>
    ''' add:Wangjh 20181016 
    ''' 插入检验待处理批次工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertContainerCheckSuspending(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSCheckIBusiness.InsertContainerCheckSuspending
        Dim strSql As String = <stirng>INSERT INTO containerchecksuspending ccs (ccs.containerid,ccs.factoryid,ccs.workflowstepid,
ccs.stepinfoid,ccs.createdate,ccs.createemployeeid,ccs.notes,ccs.isuse)
VALUES(:containerid,:factoryid,:workflowstepid,NULL,SYSDATE,:createemployeeid,:notes,0)</stirng>
        Dim insertPara(4) As OracleParameter
        insertPara(0) = DatabaseHelper.NewParameter(":containerid", para("ContainerID"), OracleType.VarChar)
        insertPara(1) = DatabaseHelper.NewParameter(":factoryid", para("FactoryID"), OracleType.VarChar)
        insertPara(2) = DatabaseHelper.NewParameter(":workflowstepid", para("WorkflowstepID"), OracleType.VarChar)
        insertPara(3) = DatabaseHelper.NewParameter(":createemployeeid", para("EmployeeID"), OracleType.VarChar)
        insertPara(4) = DatabaseHelper.NewParameter(":notes", para("Notes"), OracleType.VarChar)
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSql, insertPara)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 更新检验待处理数据为无效
    ''' add:Wangjh 20181016
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateContainerCheckSuspending(ByVal containerID As String, ByVal stepID As String) As Boolean Implements BusinessRule.IBusiness.uLMSCheckIBusiness.UpdateContainerCheckSuspending
        Dim objPara As Object()
        objPara = FillUpdateContainerCheckSuspending(containerID, stepID)
        Dim i As Integer = DatabaseHelper.ExecuteSql(objPara.First, objPara.Last)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function FillUpdateContainerCheckSuspending(ByVal containerID As String, ByVal stepID As String) As Object() Implements uLMSCheckIBusiness.FillUpdateContainerCheckSuspending
        Dim strSql As String = <string> UPDATE  containerchecksuspending css SET css.isuse=1 
                WHERE css.containerid=:containerid AND css.workflowstepid=:workflowstepid  </string>
        Dim updatePara(1) As OracleParameter
        updatePara(0) = DatabaseHelper.NewParameter(":containerid", containerID, OracleType.VarChar)
        updatePara(1) = DatabaseHelper.NewParameter(":workflowstepid", stepID, OracleType.VarChar)
        Return New Object() {strSql, updatePara}
    End Function
    ''' <summary>
    ''' 查询是否有委托计量或返工返修 1：委托计量 2：返工返修 0:都没有
    ''' add:Wangjh 20181025
    ''' </summary>
    ''' <param name="dr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IshavingReworkORDelegate(ByVal dr As DataRow) As Integer Implements uLMSCheckIBusiness.IshavingReworkORDelegate
        Dim strSql = String.Format("SELECT * FROM ENTRUSTMEASUREMENT et WHERE et.containerid='{0}' AND et.workflowstepid='{1}'",
                                    dr("ContainerID"), dr("WorkflowStepID"))
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            Return 1
        End If
        strSql = String.Format("SELECT * FROM STEPREWORKINFO et WHERE et.containerid='{0}' AND et.workflowstepid='{1}'",
                                    dr("ContainerID"), dr("WorkflowStepID"))
        If DatabaseHelper.Query_ReturnDatTable(strSql).Rows.Count > 0 Then
            Return 2
        End If
        Return 0
    End Function
    ''' <summary>
    ''' 根据报废名获取报废信息
    ''' add:Wangjh 20181220
    ''' </summary>
    ''' <param name="name"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetLossReasonbyName(ByVal name As String) As DataTable Implements uLMSCheckIBusiness.GetLossReasonbyName
        Dim strSql As String = "SELECT *  FROM lossreason ls WHERE ls.lossreasonname='" + name + "'"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

#Region "装试检验管理"

    ''' <summary>
    ''' 获取没有检验的报工工序工步
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks>add AND cw.isuse = 0 by tianFW 2018年9月29日</remarks>
    Function GetStepinfo(ByVal cid As String) As DataTable Implements uLMSCheckIBusiness.GetStepinfo
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT cw.containerstepworkreportid,cw.qty,cw.containerid,wfs.workflowstepid,wfs.workflowstepname, ")
        sb.AppendLine("so.stepinfoid,sob.stepinfoname,e.employeename Checker,e1.employeename Worker,sc.name,sc.checkpointnote FROM   ")
        sb.AppendLine("containerstepworkreport cw ")
        sb.AppendLine("LEFT JOIN Workflowstep wfs ON cw.workflowstepid=wfs.workflowstepid ")
        sb.AppendLine("LEFT JOIN stepinfo so ON cw.stepinfoid=so.stepinfoid ")
        sb.AppendLine("LEFT JOIN stepinfobase sob ON so.stepinfoid=sob.revofrcdid ")
        sb.AppendLine("LEFT JOIN employee e ON cw.checkerid=e.employeeid ")
        sb.AppendLine("LEFT JOIN employee e1 ON cw.workerid=e1.employeeid ")
        sb.AppendLine("LEFT JOIN STEPCHECKPOINT SC ON so.stepinfoid=sc.stepinfoid ")
        sb.AppendFormat("WHERE cw.containerid='{0}' AND cw.isuse = 0 ", cid)
        sb.AppendLine(" and cw.containerstepworkreportid not in (")
        sb.AppendFormat(" SELECT cc.containerstepworkreportid FROM  ContainerCheckInfo  cc where cc.containerid='{0}' and cc.isuse = 0  ", cid)
        sb.AppendLine(")")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 获取故检工步和工序数据集Add by Yuxj20180329
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetGJStepName(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetGJStepName
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                 SELECT wfs.workflowstepid,wfs.workflowstepname,sti.stepinfoid,stb.stepinfoname,
                        decode(NVL(sp.iskey,sp1.iskey),0,'否',1,'是',NVL(sp.iskey,sp1.iskey)) iskey
                 FROM workflow wf
                 LEFT JOIN workflowstep wfs ON wf.workflowid=wfs.workflowid
                 LEFT JOIN spec sp ON wfs.specid=sp.specid
                 LEFT JOIN specbase spb ON wfs.specbaseid=spb.specbaseid
                 LEFT JOIN spec sp1 ON sp1.specid=spb.revofrcdid
            </string>

        sb.AppendLine(strsql)
        If para.ContainsKey("WorkflowId") Then
            sb.AppendFormat("where wfs.workflowid='{0}'", para("WorkflowId"))
        End If

        sb.AppendLine("order by sequence asc ")

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt

    End Function

    ''' <summary>
    ''' 排故工序工步获取 Add by Yuxj20180330
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GeteGJStepInfoName(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GeteGJStepInfoName
        Dim sb As New StringBuilder
        '传入containerId 和 StepList获取工步列表
        Dim strList As String = para("StepInfoList")
        strList = strList.Substring(0, strList.Length - 1) '去掉逗号
        strList = strList.Replace(",", "','")
        sb.AppendLine(" SELECT sti.stepinfoid,stb.stepinfoname,wfs.workflowstepid,wfs.workflowstepname ")
        sb.AppendLine(" ,e.employeename Checker,e1.employeename Worker FROM  stepinfo StI  ")
        sb.AppendLine(" LEFT JOIN workflowstep wfs ON wfs.workflowstepid=cs.Workflowstepid ")
        sb.AppendLine(" LEFT JOIN stepinfobase stb ON sti.stepinfobaseid=stb.stepinfobaseid ")
        ' and csw.isuse = 0 add by tianFW 2018年9月29日
        sb.AppendLine(" LEFT JOIN containerstepworkreport csw ON csw.stepinfoid=sti.stepinfoid  and csw.isuse = 0 ")
        sb.AppendFormat(" AND  csw.containerid='{0}' and csw.stepinfoid in ('{1}')", para("ContainerId"), strList)
        sb.AppendLine(" LEFT JOIN employee e1 ON csw.workerid=e1.employeeid ")
        sb.AppendLine(" LEFT JOIN containercheckinfo cci ON cci.containerid=csw.containerid AND cci.stepinfoid=sti.stepinfoid and cci.isuse = 0 ")
        sb.AppendFormat(" AND  cci.containerid='{0}' and cci.stepinfoid in ('{1}') ", para("ContainerId"), strList)
        sb.AppendLine(" LEFT JOIN employee e2 ON cci.checkerid=e2.employeeid ")
        sb.AppendFormat("where StI.stepinfoid in ('{0}') ", strList)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 获取装配工艺的已派工但没有完工的工序工步 Add by Yuxj20180329
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSWorkFlowStepAndStepInfo(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSCheckIBusiness.GetZSWorkFlowStepAndStepInfo
        Dim ds As New DataSet
        Dim sb As New StringBuilder
        '增加StI.stepno,StI.description stepinfodes的查询 add by tianFW 2018年5月26日
        'modify by tianFW 将employeename修改为fullname 2018年6月25日
        'Modify by Yuxj20181010增加显示SpecNo
        Dim strsql As String =
            <string>
                 SELECT t.teamname,wf.workflowtype,csp.containerid,e1.fullname worker,e2.fullname checker,csw.ContainerStepWorkReportID,StI.stepno,
                        sp.specno,
                        wfs.workflowstepid,wfs.workflowid,wfs.workflowstepname,sti.stepinfoid,stb.stepinfoname,csw.qty,
                        decode(sp.iskey,0,'否',1,'是',sp.iskey) iskey,StI.description stepinfodes
                        ,st.spectypename,DECODE(wfs.IsLastStep,1,'是','否') IsLastStep--add by longb 2022-06-09
                        ,DECODE(itr.ISSUEERPMATERIALID,NULL,'未发送','已发送') SendSTATUS,wfs.sequence
                        ,MIN(csp.PLANNEDSTARTDATE) PLANNEDSTARTDATE,MAX(csp.PLANNEDFINISHDATE) PLANNEDFINISHDATE
 						,DECODE(csp.RecvEmployeeID,NULL,'未接收',DECODE(csp.STARTWORKEMPID,NULL,'已接收',decode(csw.workerid,NULL,'装配中','已报工'))) STATUS
                 FROM CONTAINERSTEPDISPATCH csp
                 LEFT JOIN containerstepworkreport csw ON csp.containerstepdispatchid=csw.containerstepdispatchid and csw.isuse = 0
                 LEFT JOIN employee e1 ON csw.workerid=e1.employeeid
                 LEFT JOIN containercheckinfo cci ON cci.containerstepworkreportid=csw.containerstepworkreportid  and cci.isuse = 0
                 LEFT JOIN employee e2 ON cci.checkerid=e2.employeeid
                 LEFT JOIN workflowstep wfs ON csp.workflowstepid=wfs.workflowstepid
                 LEFT JOIN team t ON wfs.teamid=t.teamid
                 LEFT JOIN Workflow wf ON wf.workflowid=wfs.workflowid
                 LEFT JOIN stepinfo StI ON csp.stepinfoid=StI.stepinfoid
                 LEFT JOIN stepinfobase stb ON sti.stepinfobaseid=stb.stepinfobaseid                 
                 LEFT JOIN specbase spb ON wfs.specbaseid=spb.specbaseid
                 LEFT JOIN spec sp ON sp.specid=NVL(spb.revofrcdid,wfs.specid) 
                 LEFT JOIN spectype st ON st.spectypeid=sp.spectypeid--add by longb 2022-06-09
                 LEFT JOIN issueerpmaterial im ON im.WORKFLOWSTEPID=csp.WORKFLOWSTEPID AND im.containerid=csp.containerid  --add by longb 2023-03-23
				 LEFT JOIN IssueMaterialTransport itr ON itr.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID                
            </string>

        sb.AppendLine(strsql)
        If para.ContainsKey("ContainerId") Then
            sb.AppendFormat("where csp.isuse=0 and csp.containerid='{0}'", para("ContainerId")) 'modify by longb 2023-03-17
        End If

        If para.ContainsKey("WorkflowId") Then
            sb.AppendFormat("and wfs.workflowid='{0}'", para("WorkflowId"))
        End If
        'add YangJiang 20180523
        If para.ContainsKey("WorkflowstepId") Then
            sb.AppendFormat("and wfs.workflowstepid in ({0})", para("WorkflowstepId"))
        End If
        'add by longb 2022-06-09
        sb.AppendLine(<string>
        GROUP BY t.teamname,wf.workflowtype,csp.containerid,e1.fullname,e2.fullname,csw.ContainerStepWorkReportID,StI.stepno,wfs.SEQUENCE,
         sp.specno,st.spectypename,wfs.workflowstepid,wfs.workflowid,wfs.workflowstepname,sti.stepinfoid,stb.stepinfoname,csw.qty,
         wfs.IsLastStep,sp.iskey,StI.description,DECODE(itr.ISSUEERPMATERIALID,NULL,'未发送','已发送') 
 		,DECODE(csp.RecvEmployeeID,NULL,'未接收',DECODE(csp.STARTWORKEMPID,NULL,'已接收',decode(csw.workerid,NULL,'装配中','已报工'))) 
                      </string>.Value)
        sb.AppendLine("order by wfs.sequence asc,sti.stepinfoid asc ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        For Each row As DataRow In dt.Rows 'add by longb 2022-08-30
            Dim dtc As Integer = dt.Select("containerid='" + row("containerid") + "' AND workflowstepID='" + row("workflowstepID") + "' AND STATUS='已报工'").Length
            If dtc > 0 Then
                row("STATUS") = "已报工"
            End If
        Next
        Dim dtStep As DataTable = dt.DefaultView.ToTable("DtStep", True, "ContainerId", "workflowid", "workflowstepid", "workflowstepname", "iskey", "workflowtype", "teamname", "spectypename", "IsLastStep", "SendSTATUS", "PLANNEDSTARTDATE", "PLANNEDFINISHDATE", "STATUS") 'modify by longb 2022-06-09
        Dim dtStepInfo As DataTable = dt.DefaultView.ToTable("DtStepInfo", True, "ContainerId", "qty", "workflowstepid", "workflowstepname",
                                                             "ContainerStepWorkReportID", "stepinfoid", "stepinfoname", "worker", "checker", "stepno", "specno", "stepinfodes", "workflowid")
        ds.Tables.Add(dtStep)
        ds.Tables.Add(dtStepInfo)
        Return ds
    End Function

    ''' <summary>
    ''' 验证装试报工装配表是否已有备注note信息，如果有则返回该记录的唯一ID create by tianFW 2018年5月28日
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function VerifyZSWorkreportMaterialrRecordIsExistInfo(ByVal dto As ContainerStepWorkReportDTO, ByRef note As String) As String Implements uLMSCheckIBusiness.VerifyZSWorkreportMaterialrRecordIsExistInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT wmr.workreportmaterialrecordid,wmr.note FROM Workreportmaterialrecord wmr")
        strsql.AppendFormat(" WHERE wmr.containerid='{0}' AND wmr.workflowstepid='{1}' AND wmr.stepinfoid='{2}'", dto.ContainerID, dto.WorkflowstepID, dto.StepInfoID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        If dt.Rows.Count > 0 Then
            If dt.Rows(0).IsNull("note") Then
                note = ""
            Else
                note = dt.Rows(0)("note").ToString()
            End If
            Return dt.Rows(0)("workreportmaterialrecordid")
        Else
            Return ""
        End If
    End Function

    ''' <summary>
    ''' 更新装试报工装配的备注信息 create  by tianFW 2018年5月28日 
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateZSWorkreportMaterialrRecordNote(ByVal dto As ContainerStepWorkReportDTO) As Boolean Implements uLMSCheckIBusiness.UpdateZSWorkreportMaterialrRecordNote
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE Workreportmaterialrecord wmr SET")
        strsql.AppendFormat(" wmr.note='{0}' WHERE wmr.workreportmaterialrecordid='{1}'", dto.Note, dto.WorkReportMaterialRecordID)
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取报工但没有检验的批次信息
    ''' </summary>
    ''' <param name="cidList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNoCheckStepByCid(ByVal cidList As String) As DataTable Implements uLMSCheckIBusiness.GetNoCheckStepByCid
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT csw.containerid,wfs.workflowstepid,si.Stepinfoid,ep.completestepid,csw.ContainerStepWorkReportID,1 qty FROM enginepartplan ep  ")
        sb.AppendLine("LEFT JOIN workflowstep wfs ON ep.workflowid=wfs.workflowid  ")
        sb.AppendLine("LEFT JOIN stepinfo SI ON wfs.workflowstepid=si.Workflowstepid  ")
        sb.AppendLine("INNER JOIN containerstepworkreport csw ON csw.Containerid=nvl(ep.containerid,ep.enginecontainerid)  ")
        sb.AppendLine("AND csw.Workflowstepid||csw.stepinfoid=wfs.workflowstepid||si.Stepinfoid  ")
        sb.AppendLine("LEFT JOIN containercheckinfo cc ON csw.containerstepworkreportid=cc.containerstepworkreportid  AND cc.isuse=0  ")
        sb.AppendFormat("WHERE cc.containercheckinfoid IS NULL AND  csw.containerid IN ('{0}') AND csw.Isuse=0 ",
                        cidList.Replace(",", "','"))
        sb.AppendLine("ORDER BY csw.containerid ASC ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
#End Region

#Region "Yuxj20200630机加检验优化速度"
    ''' <summary>
    ''' 检验前检查
    ''' </summary>
    ''' <param name="workReportIDList"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ContainerCheckValidateList(ByVal workReportIDList As String, ByVal type As Integer) As DataTable Implements uLMSCheckIBusiness.ContainerCheckValidateList
        Dim strSql As String = ""
        workReportIDList = workReportIDList.Replace(",", "','")
        If type = 0 Then '验证工序检'
            strSql = <string>
                    SELECT c.containername FROM containercheckinfo cci
                    LEFT JOIN container c ON c.containerid = cci.containerid
                    WHERE cci.isuse = 0
                </string>
            strSql += String.Format(" AND cci.containerstepworkreportid in ('{0}')", workReportIDList)
        End If

        If type = 1 Then '验证成检'
            strSql = <string>
                        SELECT c.containername FROM containerstepworkreport csw
                        LEFT JOIN container c ON c.containerid=cci.containerid
                        WHERE csw.containerfinishedcheckinfoid IS NOT NULL AND csw.isuse = 0
                     </string>
            strSql += String.Format(" AND csw.containerstepworkreportid='{0}'", workReportIDList)
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return dt
    End Function

    ''' <summary>
    ''' 获取批次的所有工序检和首检检验的合格数
    ''' creat:Wangjh 2017-6-14
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllAccepQtyList(ByVal containerIDList As String) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetAllAcceptQtyList
        Dim strSql As String = <string>
SELECT CCI.ACCEPTANCEQTY,C.QTY,C.CONTAINERID,NVL(CSW.LOSSQTY,0) LOSSQTY
FROM CONTAINERCHECKINFO CCI
LEFT JOIN CONTAINER C ON C.CONTAINERID = CCI.CONTAINERID
LEFT JOIN CURRENTSTATUS CU ON CU.CURRENTSTATUSID = C.CURRENTSTATUSID
LEFT JOIN CONTAINERSTEPWORKREPORT CSW ON CSW.CONTAINERSTEPWORKREPORTID = CCI.CONTAINERSTEPWORKREPORTID </string>
        strSql += String.Format(" WHERE CCI.CONTAINERID IN ('{0}') AND CCI.WORKFLOWSTEPID = CU.WORKFLOWSTEPID AND CCI.ISUSE = 0", containerIDList)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetAllAccepQtyList = dt
    End Function

    ''' <summary>
    ''' 工序检验确认前验证是否存在无效的报工 add by zhangrj 2023-04-20
    ''' </summary>
    ''' <param name="workReportIDList"></param>
    ''' <param name="type">0：多ID查询，1：单ID查询</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildWorkReportForCheck(ByVal workReportIDList As String, ByVal type As Integer) As Boolean Implements uLMSCheckIBusiness.IsVaildWorkReportForCheck
        workReportIDList = workReportIDList.Replace(",", "','")
        Dim strSql = "SELECT csw.isuse FROM containerstepworkreport csw "
        If type = 0 Then '多个ID查询
            strSql += String.Format(" WHERE csw.containerstepworkreportid IN ('{0}')", workReportIDList)
        ElseIf type = 1 Then '单个ID查询
            strSql += String.Format(" WHERE csw.containerstepworkreportid = '{0}'", workReportIDList)
        End If
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Select("isuse = 1").Length > 0 Then
            Return False
        End If
        Return True
    End Function

#End Region

#Region "装试故检"
    ''' <summary>
    ''' Add by Yuxj20180531 单独写故检查询的方法
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetGJCheckContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetGJCheckContainer
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                 SELECT wf.workflowid,pf.productfamilyName,u.attributevalue machine,mfg.mfgordername,mfg.mfgorderid,c.togetherflag,c.ContainerName, pb.productname,
                        p.productid,p.description,c.plannedstartdate,c.plannedcompletiondate,c.containerid,
                        wfs.workflowstepid,wfs.workflowstepname StepName,wfb.workflowname||':'||wf.workflowrevision workflownamever,wf.description wfdesc,ep.containerno 
                 FROM enginepartplan ep
                INNER JOIN container c ON  c.containerid=NVL(EP.CONTAINERID, EP.ENGINECONTAINERID) and c.status=1 --Yuxj20181217 ep.containerid=c.containerid   
                LEFT JOIN workflow wf ON ep.workflowid=wf.workflowid
                LEFT JOIN (SELECT DISTINCT wfs.workflowid,wfs.workflowstepid FROM workflowstep wfs 
                                  LEFT JOIN team t ON t.teamid=wfs.teamid WHERE t.teamname LIKE '%故检%') wfs1 
                                    ON wfs1.workflowid=wf.workflowid
                LEFT JOIN mfgorder mfg ON ep.mfgorderid=mfg.mfgorderid
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid
                LEFT JOIN product p ON p.productid=ep.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN workflowstep wfs ON wfs.workflowstepid=ep.Workflowstepid
                --LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid
                LEFT JOIN workflowbase wfb ON wf.workflowbaseid=wfb.workflowbaseid
                --Add by Yuxj20180710 获取机型信息
                LEFT JOIN workflowproductfamilylist wfp ON wfs.workflowid=wfp.workflowid
                LEFT JOIN productfamily pf ON wfp.productfamilylistid=pf.productfamilyid 
                LEFT JOIN workflowstep ws2 ON ws2.workflowid=wf.workflowid --部件工序
                LEFT JOIN containercheckinfo cch ON cch.containerid=c.containerid  AND cch.isuse=0  AND cch.workflowstepid=DECODE(wf.phasenode,'返件和故检',ws2.workflowstepid,wfs1.workflowstepid)
                WHERE u.userattributename='发动机' AND ep.usecompleted='0'  AND cch.containercheckinfoid IS NULL
            </string>
        sb.AppendLine(strsql)
        sb.AppendLine(" and (wf.phasenode='返件和故检' OR wfs1.workflowid IS NOT NULL) ")
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If

        'Modify by Yuxj20190301
        If para.ContainsKey("TaskType") Then
            If para("TaskType") = "施工单任务" Then
                sb.AppendLine(" and wf.workflowtype='施工工艺' ")
            ElseIf para("TaskType") = "订单任务" Then
                sb.AppendLine(" and nvl(wf.workflowtype,'1') <>'施工工艺' ")
            End If
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and c.togetherflag like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("FactoryID") Then
            sb.AppendFormat(" and wf.FactoryID='{0}' ", para("FactoryID")) 'Modify by Yuxj20180702
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("MachineStart"))
        End If

        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and pb.ProductName='{0}' ", para("ProductName"))
        End If

        sb.AppendLine(<string>GROUP BY  wf.workflowid,pf.productfamilyName,u.attributevalue,mfg.mfgordername,mfg.mfgorderid,c.togetherflag,c.ContainerName, pb.productname,
                        p.productid,p.description,c.plannedstartdate,c.plannedcompletiondate,c.containerid,
                        wfs.workflowstepid,wfs.workflowstepname,wfb.workflowname,wf.workflowrevision,wf.description,ep.containerno  
                       </string>.Value)

        sb.AppendLine(" Order by u.attributevalue,mfg.mfgordername,c.togetherflag,c.ContainerName asc")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function


    ''' <summary>
    ''' 获取装配工艺故检对应的工序工步 Add by Yuxj20180531
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetGJZSStepAndStepInfo(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSCheckIBusiness.GetGJZSStepAndStepInfo
        Dim ds As New DataSet
        Dim sb As New StringBuilder
        '增加StI.stepno,StI.description stepinfodes的查询 add by tianFW 2018年5月26日
        Dim strsql As String =
            <string>
                 SELECT  wf.workflowtype, t.teamname,cci.containercheckinfoid,cci.containerid,e2.employeename,e2.fullname checker,StI.stepno,
                        wfs.workflowstepid,wfs.workflowid,wfs.workflowstepname,sti.stepinfoid,stb.stepinfoname,
                        decode(NVL(sp.iskey,sp1.iskey),0,'否',1,'是',NVL(sp.iskey,sp1.iskey)) iskey,StI.description stepinfodes
                 FROM workflowstep wfs 
                 LEFT JOIN workflow wf ON wfs.workflowid=wf.workflowid
                 LEFT JOIN team t ON wfs.teamid=t.teamid                          
                 LEFT JOIN stepinfo StI ON wfs.workflowstepid=sti.workflowstepid                
                 LEFT JOIN stepinfobase stb ON sti.stepinfobaseid=stb.stepinfobaseid
                 LEFT JOIN spec sp ON wfs.specid=sp.specid
                 LEFT JOIN specbase spb ON wfs.specbaseid=spb.specbaseid
                 LEFT JOIN spec sp1 ON sp1.specid=spb.revofrcdid                 
            </string>
        sb.AppendLine(strsql)
        sb.AppendFormat(" LEFT JOIN containercheckinfo cci ON wfs.workflowstepid||sti.stepinfoid=cci.workflowstepid||cci.stepinfoid and cci.containerid='{0}' ", para("ContainerId"))
        sb.AppendLine(" LEFT JOIN employee e2 ON cci.checkerid=e2.employeeid  ")

        sb.AppendFormat("where wfs.workflowid='{0}'", para("WorkflowId"))

        If para.ContainsKey("WorkflowstepId") Then
            sb.AppendFormat("and wfs.workflowstepid in ({0})", para("WorkflowstepId"))
        End If

        sb.AppendLine("order by wfs.sequence asc,sti.stepinfoid asc ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim dtStep As DataTable = dt.DefaultView.ToTable("DtStep", True, "ContainerId", "workflowid", "workflowstepid", "workflowstepname", "iskey", "workflowtype", "teamname")
        Dim dtStepInfo As DataTable = dt.DefaultView.ToTable("DtStepInfo", True, "ContainerId", "workflowstepid", "workflowstepname",
                                                             "containercheckinfoid", "stepinfoid", "stepinfoname", "stepinfodes", "checker", "stepno")
        ds.Tables.Add(dtStep)
        ds.Tables.Add(dtStepInfo)
        Return ds
    End Function
    ''' <summary>
    ''' 添加装试批次检验记录V2.0
    ''' Create:Yuxj20180601 装试单独使用，增加工步信息
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddZSContainerCheckInfo(ByVal entity As ContainerCheckInfoDTO) As Boolean Implements IBusiness.uLMSCheckIBusiness.AddZSContainerCheckInfo

        Dim strsql As String =
            <string>
                    INSERT INTO CONTAINERCHECKINFO(CONTAINERID,FACTORYID,WORKFLOWSTEPID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,LOSSQTY,REWORKQTY,DEFECTQTY,
                    CONTAINERCHECKINFOID,CREATEDATE,CHECKNOTE,FIRSTCHECKBILLNO,CONTAINERSTEPWORKREPORTID,ISUSE,ISTRANSFER,CHECKTYPENOTE,Stepinfoid)
                    VALUES(:containerid,:factoryid,:workflowstepid,:checkerid,:checkqty,:acceptanceqty,:lossqty,:reworkqty,:defectqty,
                    :containercheckinfoid,sysdate,:checknote,:firstcheckbillno,:containerstepworkreportid,0,0,:CHECKTYPENOTE,:Stepinfoid)
                </string>
        Dim insertpar(14) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":checkqty", entity.CheckQty, OracleType.Number)
        insertpar(5) = DatabaseHelper.NewParameter(":acceptanceqty", entity.AcceptanceQty, OracleType.Number)
        insertpar(6) = DatabaseHelper.NewParameter(":lossqty", entity.LossQty, OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":reworkqty", entity.ReworkQty, OracleType.Number)
        insertpar(8) = DatabaseHelper.NewParameter(":defectqty", entity.DefectQty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":containercheckinfoid", entity.ContainerCheckInfoID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":checknote", entity.CheckNote, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":firstcheckbillno", entity.FirstCheckBillNo, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":CHECKTYPENOTE", entity.CheckTypeNote, OracleType.Number)

        insertpar(14) = DatabaseHelper.NewParameter(":StepInfoID", entity.StepInfoID, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

#End Region

#Region "批量检验"
    ''' <summary>
    ''' 获取批量检验报工工序 add by zhangrj 20220620
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerBatchCheckList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetContainerBatchCheckList
        Dim result As New uMESProcessResult
        Try
            Dim strSql = <string>
SELECT * FROM (SELECT distinct cci.containername,cci.containerid,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,
cci.mfgordername,cci.productname,cci.containertype,cci.togetherflag,cci.workflowid,
DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
cci.workflowstepname,cci.workflowstepid,cci.workflowstepsequence as sequence,csr.checktype,csr.workerid,cci.factoryid,
e.employeename,csr.qty,to_char(csr.createdate,'yyyy-MM-dd') createdate,
csr.CheckerID,cci.qty as WipQty,csr.firstcheckcontainerno,csr.containerstepworkreportid,ws.workflowstepid as ReportStepId,ws.workflowstepname AS ReportStepName
 FROM containerstepworkreport csr 
LEFT JOIN containercheckinfo cc ON cc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN 
(SELECT csr1.* FROM containerstepworkreport csr1 
LEFT JOIN containercheckinfo cc1 ON cc1.containerstepworkreportid = csr1.containerstepworkreportid
WHERE cc1.containercheckinfoid IS NULL AND csr1.checktype = '1' and csr1.isuse = 0
) s ON s.containerid = csr.containerid AND s.workflowstepid = csr.workflowstepid
LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csr.workflowstepid
LEFT JOIN containerstepworkreportchild cswc ON cswc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN employee e ON e.employeeid = csr.workerid
LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND  cci.STEPCHECKNUM=1 AND cci.firstchecknum {1} 1 AND cci.SplitNum=0
AND csr.checktype in ('2','3','4') AND s.containerstepworkreportid IS NULL
AND csr.workflowstepid = cci.workflowstepid
AND (CASE WHEN cci.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN cci.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN cci.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN cci.SHIPNUM ELSE 5 END)>4
ORDER BY cci.containerid) ck
WHERE ck.factoryid = '{0}'
                     </string>.Value
            strSql = String.Format(strSql, para("factoryID"), "<>")

            If para.Keys.Contains("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((ck.containertype <> '7' and ck.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (ck.containertype = '7' and ck.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If
            If para.Keys.Contains("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql += " AND ck.mfgordername = '" + para("ScanOrderName") + "'"
                End If
            End If
            If para.Keys.Contains("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql += " AND ck.productname like '%" + para("ProductName") + "%'"
                End If
            End If
            If para.Keys.Contains("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND ck.containername like '%" + para("ContainerName") + "%'"
                End If
            End If
            If para.Keys.Contains("StepName") Then
                If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                    strSql += " AND ck.workflowstepname like '%" + para("StepName") + "%'"
                End If
            End If
            If para.Keys.Contains("CheckerID") Then
                If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                    strSql += " AND ck.CheckerID = '" + para("CheckerID") + "'"
                End If
            End If
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = "出错了,原因：" + ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 批量保存检验信息 add by zhangrj 20220620
    ''' </summary>
    ''' <param name="checkList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveBatchCheckInfo(ByVal checkList As List(Of ContainerCheckInfoDTO), ByVal paras As List(Of Dictionary(Of String, Object))) As uMESProcessResult Implements uLMSCheckIBusiness.SaveBatchCheckInfo
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            For Each checkInfo As ContainerCheckInfoDTO In checkList
                Dim strsql As String = <string>
                    INSERT INTO CONTAINERCHECKINFO(CONTAINERID,FACTORYID,WORKFLOWSTEPID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,LOSSQTY,REWORKQTY,DEFECTQTY,
                    CONTAINERCHECKINFOID,CREATEDATE,CHECKNOTE,FIRSTCHECKBILLNO,CONTAINERSTEPWORKREPORTID,ISUSE,ISTRANSFER,CHECKTYPENOTE,FIRSTCHECKCONTAINERNO,AcceptanceQty2)
                    VALUES
                </string>
                strsql += String.Format("('{0}',", checkInfo.ContainerID)
                strsql += String.Format("'{0}',", checkInfo.FactoryID)
                strsql += String.Format("'{0}',", checkInfo.WorkflowstepID)
                strsql += String.Format("'{0}',", checkInfo.CheckerID)
                strsql += String.Format("'{0}',", checkInfo.CheckQty)
                strsql += String.Format("'{0}',", checkInfo.AcceptanceQty)
                strsql += String.Format("'{0}',", checkInfo.LossQty + checkInfo.SpecimenLossQty)
                strsql += String.Format("'{0}',", checkInfo.ReworkQty)
                strsql += String.Format("'{0}',", checkInfo.DefectQty)
                strsql += String.Format("'{0}',", checkInfo.ContainerCheckInfoID)
                strsql += String.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", DateTime.Now.ToString())
                strsql += String.Format("'{0}',", checkInfo.CheckNote)
                strsql += String.Format("'{0}',", checkInfo.FirstCheckBillNo)
                strsql += String.Format("'{0}',", checkInfo.ContainerStepWorkReportID)
                strsql += "'0','0',"
                strsql += String.Format("'{0}',", checkInfo.CheckTypeNote)
                strsql += String.Format("'{0}',", checkInfo.FirstCheckContainerNo)
                strsql += String.Format("'{0}')", checkInfo.AcceptanceQty2)
                sqlList.Add(strsql)
                strsql = ""
                strsql = <string> UPDATE  containerchecksuspending css SET css.isuse=1 
                WHERE css.containerid='{0}' AND css.workflowstepid='{1}'  </string>
                strsql = String.Format(strsql, checkInfo.ContainerID, checkInfo.WorkflowstepID)
                sqlList.Add(strsql)
            Next
            '更新自动报工的分厂和报工人
            For Each para As Dictionary(Of String, Object) In paras
                Dim updateSql As String = " UPDATE containerstepworkreport csw SET checktype=checktype "
                'If para.ContainsKey("ContainerFinishedCheckInfoID") Then
                '    updateSql += String.Format(" ,containerfinishedcheckinfoid ='{0}' ", para("ContainerFinishedCheckInfoID"))
                'End If
                If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
                    updateSql += String.Format(" ,FactoryID ='{0}' ", para("FactoryID"))
                End If
                If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
                    updateSql += String.Format(" ,workerid ='{0}' ", para("WorkerID"))
                End If
                'If para.ContainsKey("LossQty") Then '更新报废数
                '    updateSql += String.Format(" ,LossQty ={0} ", para("LossQty"))
                'End If
                updateSql += String.Format(" WHERE csw.containerstepworkreportid= '{0}'", para("ContainerStepWorkReportID"))
                If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
                    updateSql += " AND csw.FactoryID is null"
                End If
                If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
                    updateSql += " AND csw.workerid is null"
                End If
                sqlList.Add(updateSql)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "检验成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "检验出错，原因：" + ex.Message
        End Try
        Return result
    End Function
#End Region

#Region "流水卡检验"
    ''' <summary>
    ''' 获取流水卡检验批次 add by zhangrj 2022-07-20
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewContainerCheckList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetNewContainerCheckList
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String
            strSql = <string>
SELECT distinct c.mfgordername, c.containerid,c.containername,c.containertype,
c.workflowid,c.workflowstepid as currworkflowstepid,c.workflowstepsequence as sequence, 
c.workflowstepname as currworkflowstepname,c.qty as WipQty,c.firstchecknum
FROM containercurrentinfo c
LEFT JOIN containerstepworkreport csr ON csr.containerid = c.containerid AND csr.isuse = 0
LEFT JOIN containercheckinfo cc ON cc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN containerstepworkreportchild cswc ON cswc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN containerstepnoship csn ON csn.containerid=c.containerid AND csn.workflowstepid=c.workflowstepid AND csn.isuse=0 AND csn.noship=1 
 WHERE cc.containercheckinfoid IS NULL AND c.isinstore in (-1,0) AND c.containerstatus=1 
and (c.stepchecknum = 1 OR c.firstchecknum = 1) AND c.splitnum = 0
 AND (CASE WHEN c.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN c.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN c.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN c.SHIPNUM ELSE 5 END)>4
 AND csr.checktype IN ('1','2')
and c.factoryid = '{0}'
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"), "<>")

            If para.Keys.Contains("ScanContainerName") Then '扫描流水卡
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((c.containertype <> '7' and c.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (c.containertype = '7' and c.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If

            If para.Keys.Contains("ContainerName") Then '流水卡
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND c.containername like '%" + para("ContainerName") + "%'"
                End If
            End If

            If para.Keys.Contains("CheckerID") Then '
                If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                    strSql += " AND csr.CheckerID = '" + para("CheckerID") + "'"
                End If
            End If

            strSql += " ORDER BY c.containerid"
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function
    ''' <summary>
    ''' 获取批次的报工记录，且没有检验的
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewStepCheckByContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetNewStepCheckByContainer
        Dim strSql As String
        strSql = <string>
SELECT ws.workflowstepid,csr.qty,csr.workerid,csr.containerstepworkreportid,csr.checktype
FROM containercurrentinfo c
LEFT JOIN containerstepworkreport csr ON csr.containerid = c.containerid AND csr.isuse = 0
LEFT JOIN containercheckinfo cc ON cc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN containerstepworkreportchild cswc ON cswc.containerstepworkreportid = csr.containerstepworkreportid
LEFT JOIN WORKFLOWSTEP WS ON WS.workflowstepid = csr.workflowstepid
where cc.containercheckinfoid IS NULL  AND csr.checktype IN ('1','2')
 and c.containerid = '{0}' and c.isinstore in (-1,0) AND c.containerstatus=1 
                     </string>.Value
        strSql = String.Format(strSql, para("Containerid"), "<>")
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function
#End Region

#Region "首件二检"
    ''' <summary>
    ''' 获取二检待检列表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerSecondCheckList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetContainerSecondCheckList
        Dim result As New uMESProcessResult
        Try
            Dim strSql = <string>
SELECT c.containername,c.containerid,
c.mfgordername,c.productname,c.containertype,c.togetherflag,c.workflowid,
DECODE(c.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
c.workflowstepname,c.workflowstepid,c.workflowstepsequence as sequence,csw.checktype,csw.workerid,c.factoryid,
e.employeename,csw.qty,to_char(csw.createdate,'yyyy-MM-dd') createdate,
csw.CheckerID,c.qty as WipQty,csw.containerstepworkreportid
FROM containercurrentinfo c
LEFT JOIN containerstepworkreport csw ON csw.containerid = c.containerid 
LEFT JOIN containercheckinfo cci ON cci.containerstepworkreportid = csw.containerstepworkreportid
LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
LEFT JOIN employee e ON e.employeeid = csw.workerid
WHERE csw.checktype = 1 AND cci.containercheckinfoid IS NULL AND c.firstchecknum = 1 AND csw.secondcheckdate IS NULL
AND c.isinstore in (-1,0) AND c.containerstatus=1 AND c.splitnum = 0
AND csw.workflowstepid = c.workflowstepid
AND (CASE WHEN c.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN c.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN c.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN c.SHIPNUM ELSE 5 END) > 4
AND c.factoryid = '{0}'
                     </string>.Value
            strSql = String.Format(strSql, para("factoryID"), "<>")

            If para.Keys.Contains("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((c.containertype <> '7' and c.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (c.containertype = '7' and c.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If
            If para.Keys.Contains("ScanOrderName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                    strSql += " AND c.mfgordername = '" + para("ScanOrderName") + "'"
                End If
            End If
            If para.Keys.Contains("ProductName") Then
                If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                    strSql += " AND c.productname like '%" + para("ProductName") + "%'"
                End If
            End If
            If para.Keys.Contains("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND c.containername like '%" + para("ContainerName") + "%'"
                End If
            End If
            If para.Keys.Contains("StepName") Then
                If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                    strSql += " AND c.workflowstepname like '%" + para("StepName") + "%'"
                End If
            End If
            If para.Keys.Contains("CheckerID") Then
                If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                    strSql += " AND c.CheckerID = '" + para("CheckerID") + "'"
                End If
            End If
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = "出错了,原因：" + ex.Message
        End Try
        Return result
    End Function
#End Region

#Region " 工步检验  add by zhangrj 2022-08-05"
    ''' <summary>
    ''' 获取工步待检验批次（未启用）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepInfoCheckData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetContainerStepInfoCheckData
        Dim strSelect As String =
            <string>
            SELECT (cci.productname||'/'||cci.containerno) ProductBatch,
cci.mfgordername,cci.productname,cci.productid,cci.containertype,cci.togetherflag,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,
DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,cci.firstchecknum,cci.stepchecknum,
cci.workflowstepname,cci.workflowstepid,sum(cci.qty) as WipQty,cci.factoryid,cci.containerno,SUM(csr.qty) AS reportQty
 FROM containerstepworkreport2 csr 
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid and cc.isuse = 0
LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 
--AND  (cci.STEPCHECKNUM=1 OR cci.firstchecknum = 1) 
AND cci.SplitNum=0
AND csr.workflowstepid = cci.workflowstepid
AND (CASE WHEN cci.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN cci.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN cci.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN cci.SHIPNUM ELSE 5 END)>4
AND cci.factoryid = '{0}'
            </string>.Value
        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")

        If para.Keys.Contains("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                strSelect += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
                strSelect += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
            End If
        End If
        If para.Keys.Contains("ScanOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                strSelect += " AND cci.mfgordername = '" + para("ScanOrderName") + "'"
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSelect += " AND cci.productname like '%" + para("ProductName") + "%'"
            End If
        End If
        If para.Keys.Contains("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strSelect += " AND cci.containername like '%" + para("ContainerName") + "%'"
            End If
        End If
        If para.Keys.Contains("StepName") Then
            If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                strSelect += " AND cci.workflowstepname like '%" + para("StepName") + "%'"
            End If
        End If
        If para.Keys.Contains("CheckerID") Then
            If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                strSelect += " AND csr.CheckerID = '" + para("CheckerID") + "'"
            End If
        End If

        strSelect += <string>
GROUP BY cci.mfgordername,cci.productname,cci.productid,cci.containertype,cci.togetherflag,cci.workflowid,s.stepinfoid,s.description,s.stepseq,
cci.containertype,cci.workflowstepname,cci.workflowstepid,cci.qty,cci.factoryid,cci.containerno,cci.firstchecknum,cci.stepchecknum
order by s.stepseq
                   </string>.Value

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取待检验工步详细（未启用）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepInfoCheckDetail(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetContainerStepInfoCheckDetail
        Dim strSelect As String =
            <string>SELECT cci.containerid,cci.containername,cci.containertype,cci.productid,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,cci.firstchecknum,cci.stepchecknum,
cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containerno,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid
 FROM containerstepworkreport2 csr 
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
LEFT JOIN employee e ON e.employeeid = csr.workerid
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 
--AND  (cci.STEPCHECKNUM=1 OR cci.firstchecknum = 1) 
AND cci.SplitNum=0
AND csr.workflowstepid = cci.workflowstepid
AND (CASE WHEN cci.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN cci.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN cci.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN cci.SHIPNUM ELSE 5 END)>4
AND cci.factoryid = '{0}'
            </string>.Value

        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")
        strSelect += " AND cci.mfgordername = '" + para("MfgOrderName") + "'"
        strSelect += " AND cci.productid = '" + para("ProductId") + "'"
        strSelect += " AND cci.workflowid = '" + para("WorkflowId") + "'"
        strSelect += " AND cci.workflowstepid = '" + para("WorkflowstepId") + "'"
        strSelect += " AND cci.togetherflag = '" + para("TogetherFlag") + "'"
        strSelect += " AND s.stepinfoid = '" + para("StepinfoId") + "'"

        If para("IsFirstCheck").ToString = "1" Then
            strSelect += " AND csr.checktype = '1'"
        Else
            strSelect += " AND csr.checktype <> '1'"
        End If

        If para.Keys.Contains("CheckerID") Then
            If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                strSelect += " AND csr.CheckerID = '" + para("CheckerID") + "'"
            End If
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 获取工步待检验批次（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRBContainerStepInfoCheckData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetRBContainerStepInfoCheckData
        Dim strSelect1 As String =
            <string>
                SELECT (cci.productname||'/'||cci.containerno) ProductBatch,
                cci.mfgordername,cci.productname,cci.productid,cci.containertype,cci.togetherflag,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,
                DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
                cci.workflowstepname,cci.workflowstepid,sum(cci.qty) as WipQty,cci.factoryid,SUM(csr.qty) AS reportQty,'0' iscombinate
                 FROM containerstepworkreport2 csr 
                LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
                LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
                LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
                LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
                WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
                AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND csr.workflowstepid = cci.workflowstepid
                AND cci.factoryid = '{0}'
                AND NOT EXISTS (SELECT 1 FROM containercombinate cb WHERE cb.containerid = csr.containerid)
                {1}
                GROUP BY cci.mfgordername,cci.productname,cci.productid,cci.containertype,cci.togetherflag,cci.workflowid,s.stepinfoid,s.description,s.stepseq,
                cci.containertype,cci.workflowstepname,cci.workflowstepid,cci.qty,cci.factoryid,cci.containerno
            </string>.Value
        Dim strSelect2 As String =
            <string>
                SELECT cb.containercombinatename ProductBatch,cci.mfgordername,cci.productname,cci.productid,cci.containertype,'' togetherflag,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,
                DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
                cci.workflowstepname,cci.workflowstepid,sum(cci.qty) as WipQty,cci.factoryid,SUM(csr.qty) AS reportQty,'1' iscombinate
                FROM containercombinate cb
                LEFT JOIN Containercurrentinfo cci ON cci.containerid = cb.containerid
                LEFT JOIN containerstepworkreport2 csr ON csr.containerid = cci.containerid AND csr.workflowstepid = cci.workflowstepid
                LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
                LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
                WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
                AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND cci.factoryid = '{0}'
                {1}
                GROUP BY cb.containercombinatename,cci.mfgordername,cci.productname,cci.productid,cci.workflowid,s.stepno,s.stepinfoid,s.description,s.stepseq,
                cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containertype
            </string>.Value
        Dim strWhere = String.Empty
        Dim strSelect = String.Format(<string>
                                          {0}
                                          UNION ALL
                                          {1}
                                      </string>.Value, strSelect1, strSelect2)
        If para.Keys.Contains("CombinateName") Then
            If Not String.IsNullOrWhiteSpace(para("CombinateName")) Then
                strSelect = strSelect2
                strWhere += " AND cb.containercombinatename like '%" + para("CombinateName") + "%'"
            End If
        End If

        If para.Keys.Contains("ScanContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                strWhere += " AND cci.containername = '" + para("ScanContainerName") + "'"
            End If
        End If
        If para.Keys.Contains("ScanOrderName") Then
            If Not String.IsNullOrWhiteSpace(para("ScanOrderName")) Then
                strWhere += " AND cci.mfgordername = '" + para("ScanOrderName") + "'"
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strWhere += " AND cci.productname like '%" + para("ProductName") + "%'"
            End If
        End If
        If para.Keys.Contains("ContainerName") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                strWhere += " AND cci.containername like '%" + para("ContainerName") + "%'"
            End If
        End If
        If para.Keys.Contains("StepName") Then
            If Not String.IsNullOrWhiteSpace(para("StepName")) Then
                strWhere += " AND cci.workflowstepname like '%" + para("StepName") + "%'"
            End If
        End If
        If para.Keys.Contains("CheckerID") Then
            If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                strWhere += " AND csr.CheckerID = '" + para("CheckerID") + "'"
            End If
        End If

        strSelect = String.Format(strSelect, para("FactoryID").ToString, strWhere)

        strSelect = String.Format("select * from ({0})k order by k.ProductBatch,k.workflowstepid,k.stepseq", strSelect)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取待检验工步详细（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRBContainerStepInfoCheckDetail(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetRBContainerStepInfoCheckDetail
        Dim strSelect As String = String.Empty
        If para("IsCombinate") = "0" Then
            strSelect = <string>SELECT cci.containerid,cci.containername,cci.containertype,cci.productid,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,
                        cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containerno,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid,'0' iscombinate
                         FROM containerstepworkreport2 csr 
                        LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
                        LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
                        LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
                        LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
                        LEFT JOIN stepinfobase sb ON sb.stepinfobaseid = s.stepinfobaseid
                        LEFT JOIN employee e ON e.employeeid = csr.workerid
                        WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
                        AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND csr.workflowstepid = cci.workflowstepid
                        AND cci.factoryid = '{0}' AND cci.mfgordername = '{1}' AND cci.productid = '{2}' AND cci.workflowid = '{3}'
                        AND cci.workflowstepid = '{4}' AND cci.togetherflag = '{5}' AND s.stepinfoid = '{6}'
            </string>.Value
            strSelect = String.Format(strSelect, para("FactoryID").ToString, para("MfgOrderName"), para("ProductId"), para("WorkflowId"), para("WorkflowstepId"), para("TogetherFlag"), para("StepinfoId"))
        Else
            strSelect = <string>SELECT cci.containerid,cci.containername,cci.containertype,cci.productid,cci.workflowid,s.stepinfoid,s.description stepinfoname,s.stepseq,
                        cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containerno,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid,'1' iscombinate
                        FROM containercombinate cb
                        LEFT JOIN Containercurrentinfo cci ON cci.containerid = cb.containerid
                        LEFT JOIN containerstepworkreport2 csr ON csr.containerid = cci.containerid AND csr.workflowstepid = cci.workflowstepid
                        LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
                        LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
                        LEFT JOIN employee e ON e.employeeid = csr.workerid
                        WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
                        AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND cci.factoryid = '{0}'
                        AND cb.containercombinatename = '{1}' AND s.stepinfoid = '{2}'
            </string>.Value
            strSelect = String.Format(strSelect, para("FactoryID").ToString, para("ProductBatch"), para("StepinfoId"))
        End If

        If para.Keys.Contains("CheckerID") Then
            If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
                strSelect += " AND csr.CheckerID = '" + para("CheckerID") + "'"
            End If
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 获取工步报工详细（弹窗明细）
    ''' </summary>
    ''' <param name="reportIDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepInfoReportList(ByVal reportIDs As String) As DataTable Implements uLMSCheckIBusiness.GetStepInfoReportList
        Dim strSelect As String =
            <string>
            SELECT cci.containerid,cci.containername,c.containername as childcontainername,c.containerid as childcontainerid,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid,
            csr.selfcheckqualifiedqty,csr.selfcheckoutoftoleranceqty,NVL(csrc.selfchecklossqty,csr.selfchecklossqty) selfchecklossqty,csr.createdate,csr.stepinfoid,
            csr.specimenqty,csr.specimenlossqty,cci.workflowid,cci.workflowstepid,cci.workflowstepsequence AS sequence,cci.factoryid,
            (SELECT wm_concat(csr2.firstcheckcontainerno) 
            FROM containerstepworkreport2 csr2
            WHERE csr2.workerid = csr.workerid AND csr2.containerid = csr.containerid and csr2.isuse = 0
            AND csr2.workflowstepid = csr.workflowstepid AND csr2.stepinfoid = csr.stepinfoid
            ) AS firstcheckcontainerno
             FROM containerstepworkreport2 csr 
            LEFT JOIN containercurrentinfo cci ON cci.containerid = csr.containerid
            LEFT JOIN containerstepworkreportchild2 csrc ON csrc.containerstepworkreportid = csr.containerstepworkreportid
            LEFT JOIN container c ON c.containerid = csrc.childcontainerid
            LEFT JOIN employee e ON e.employeeid = csr.workerid
            WHERE csr.containerstepworkreportid IN ({0}) and csr.isuse = 0
            ORDER BY nvl(csrc.childcontainerid,cci.containerid)
            </string>.Value

        strSelect = String.Format(strSelect, reportIDs)
        Return DatabaseHelper.Query_ReturnDatTable(strSelect)
    End Function

    ''' <summary>
    ''' 验证是否存在顺序号小的工步未检验完
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsExistNoCheckedPreStepInfo(ByVal para As DataRow) As Boolean Implements uLMSCheckIBusiness.IsExistNoCheckedPreStepInfo
        Dim strSelect As String = <string>
          select * from(
         select SUM(nvl(cci.checkqty,0)) checkqty  from containercurrentinfo c
         left join stepinfo s on s.workflowstepid = c.workflowstepid
         left join containercheckinfo2 cci on cci.containerid = c.containerid and cci.workflowstepid= c.workflowstepid and cci.stepinfoid= s.stepinfoid and cci.isuse = 0
         where c.containerid = '{0}' and c.workflowstepid = '{1}' and s.stepseq {2} {3}
         AND s.revision = (SELECT s1.revision FROM stepinfo s1 WHERE s1.stepinfoid = '{5}')
         group by s.stepinfoid
         )k where k.checkqty {2} {4}
            </string>.Value
        strSelect = String.Format(strSelect, para("ContainerID").ToString, para("WorkflowStepID").ToString, "<", para("StepSeq").ToString, para("Wipqty").ToString, para("StepInfoID").ToString)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSelect)
        If dt.Rows.Count > 0 Then
            Return True
            Exit Function
        End If
        Return False
    End Function

    ''' <summary>
    ''' 工步检验前检查
    ''' </summary>
    ''' <param name="workReportIDList"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ContainerCheck2ValidateList(ByVal workReportIDList As String, ByVal type As Integer) As DataTable Implements uLMSCheckIBusiness.ContainerCheck2ValidateList
        Dim strSql As String = ""
        workReportIDList = workReportIDList.Replace(",", "','")
        If type = 0 Then '验证工序检'
            strSql = <string>
                    SELECT c.containername FROM containercheckinfo2 cci
                    LEFT JOIN container c ON c.containerid = cci.containerid
                    WHERE cci.isuse = 0
                </string>
            strSql += String.Format(" AND cci.containerstepworkreportid in ('{0}')", workReportIDList)
        End If

        If type = 1 Then '验证成检'
            strSql = <string>
                        SELECT c.containername FROM containerstepworkreport2 csw
                        LEFT JOIN container c ON c.containerid=cci.containerid
                        WHERE csw.containerfinishedcheckinfoid IS NOT NULL AND csw.isuse = 0
                     </string>
            strSql += String.Format(" AND csw.containerstepworkreportid='{0}'", workReportIDList)
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return dt
    End Function

    ''' <summary>
    ''' 保存工步检验信息
    ''' </summary>
    ''' <param name="checkList"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepInfoCheckInfo(ByVal checkList As List(Of ContainerCheckInfoDTO), ByVal paras As List(Of Dictionary(Of String, Object))) As uMESProcessResult Implements uLMSCheckIBusiness.SaveStepInfoCheckInfo
        Dim result As New uMESProcessResult
        Try
            Dim sqlList As New ArrayList
            For Each checkInfo As ContainerCheckInfoDTO In checkList
                Dim strsql As String = <string>
                    INSERT INTO CONTAINERCHECKINFO2(CONTAINERID,FACTORYID,WORKFLOWSTEPID,STEPINFOID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,LOSSQTY,REWORKQTY,DEFECTQTY,
                    CONTAINERCHECKINFOID,CREATEDATE,CHECKNOTE,FIRSTCHECKBILLNO,CONTAINERSTEPWORKREPORTID,ISUSE,ISTRANSFER,CHECKTYPENOTE,FIRSTCHECKCONTAINERNO,AcceptanceQty2)
                    VALUES
                </string>.Value
                strsql += String.Format("('{0}',", checkInfo.ContainerID)
                strsql += String.Format("'{0}',", checkInfo.FactoryID)
                strsql += String.Format("'{0}',", checkInfo.WorkflowstepID)
                strsql += String.Format("'{0}',", checkInfo.StepInfoID)
                strsql += String.Format("'{0}',", checkInfo.CheckerID)
                strsql += String.Format("'{0}',", checkInfo.CheckQty)
                strsql += String.Format("'{0}',", checkInfo.AcceptanceQty)
                strsql += String.Format("'{0}',", checkInfo.LossQty + checkInfo.SpecimenLossQty)
                strsql += String.Format("'{0}',", checkInfo.ReworkQty)
                strsql += String.Format("'{0}',", checkInfo.DefectQty)
                strsql += String.Format("'{0}',", checkInfo.ContainerCheckInfoID)
                strsql += String.Format("to_date('{0}','yyyy-mm-dd hh24:mi:ss'),", DateTime.Now.ToString())
                strsql += String.Format("'{0}',", checkInfo.CheckNote)
                strsql += String.Format("'{0}',", checkInfo.FirstCheckBillNo)
                strsql += String.Format("'{0}',", checkInfo.ContainerStepWorkReportID)
                strsql += "'0','0',"
                strsql += String.Format("'{0}',", checkInfo.CheckTypeNote)
                strsql += String.Format("'{0}',", checkInfo.FirstCheckContainerNo)
                strsql += String.Format("'{0}')", checkInfo.AcceptanceQty2)
                sqlList.Add(strsql)
                strsql = ""
                strsql = <string> UPDATE  containerchecksuspending css SET css.isuse=1 
                WHERE css.containerid='{0}' AND css.workflowstepid='{1}' and css.stepinfoid = '{2}' </string>.Value
                strsql = String.Format(strsql, checkInfo.ContainerID, checkInfo.WorkflowstepID, checkInfo.StepInfoID)
                sqlList.Add(strsql)
                '如果存在报废
                If checkInfo.LossQty > 0 Then
                    '更新工步报工报废数
                    strsql = ""
                    strsql = <string> UPDATE CONTAINERSTEPWORKREPORT2 CSW SET CSW.LOSSQTY=NVL(CSW.LOSSQTY,0) + {0} 
                             WHERE CSW.CONTAINERSTEPWORKREPORTID='{1}' and CSW.ISUSE = 0</string>.Value
                    strsql = String.Format(strsql, checkInfo.LossQty, checkInfo.ContainerStepWorkReportID)
                    sqlList.Add(strsql)
                End If
            Next
            '更新自动报工的分厂和报工人
            For Each para As Dictionary(Of String, Object) In paras
                Dim updateSql As String = " UPDATE containerstepworkreport2 csw SET checktype=checktype "
                'If para.ContainsKey("ContainerFinishedCheckInfoID") Then
                '    updateSql += String.Format(" ,containerfinishedcheckinfoid ='{0}' ", para("ContainerFinishedCheckInfoID"))
                'End If
                If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
                    updateSql += String.Format(" ,FactoryID ='{0}' ", para("FactoryID"))
                End If
                If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
                    updateSql += String.Format(" ,workerid ='{0}' ", para("WorkerID"))
                End If
                'If para.ContainsKey("LossQty") Then '更新报废数
                '    updateSql += String.Format(" ,LossQty ={0} ", para("LossQty"))
                'End If
                updateSql += String.Format(" WHERE csw.containerstepworkreportid= '{0}'", para("ContainerStepWorkReportID"))
                If para.ContainsKey("FactoryID") Then '此处更新自动报工的记录
                    updateSql += " AND csw.FactoryID is null"
                End If
                If para.ContainsKey("WorkerID") Then '此处更新自动报工的记录
                    updateSql += " AND csw.workerid is null"
                End If
                sqlList.Add(updateSql)
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            result.Result = True
            result.Message = "检验成功"
        Catch ex As Exception
            result.Result = False
            result.Message = "检验出错，原因：" + ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取批次的所有工序检和首检检验的合格数、报废数（工步）
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetAllAccepQtyListByStepInfo(ByVal containerIDList As String) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetAllAccepQtyListByStepInfo
        Dim strSql As String = <string>
SELECT CCI.ACCEPTANCEQTY,C.QTY,C.CONTAINERID,NVL(CSW.LOSSQTY,0) LOSSQTY
FROM CONTAINERCHECKINFO2 CCI
LEFT JOIN CONTAINER C ON C.CONTAINERID = CCI.CONTAINERID
LEFT JOIN CURRENTSTATUS CU ON CU.CURRENTSTATUSID = C.CURRENTSTATUSID
LEFT JOIN CONTAINERSTEPWORKREPORT2 CSW ON CSW.CONTAINERSTEPWORKREPORTID = CCI.CONTAINERSTEPWORKREPORTID and CSW.ISUSE=0</string>
        strSql += String.Format(" WHERE CCI.CONTAINERID IN ('{0}') AND CCI.WORKFLOWSTEPID = CU.WORKFLOWSTEPID AND CCI.ISUSE = 0", containerIDList)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        GetAllAccepQtyListByStepInfo = dt
    End Function

    ''' <summary>
    ''' 获取工序下的每个工步的检验数量
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoAccepQty(ByVal para As DataRow) As DataTable Implements IBusiness.uLMSCheckIBusiness.GetStepInfoAccepQty
        Dim strSql As String = <string>
                                SELECT SUM(nvl(cci.checkqty,0)) checkqty,SUM(nvl(cci.Acceptanceqty,0)) Acceptanceqty,SUM(nvl(cci.LOSSQTY,0)) LOSSQTY,s.stepinfoid,
                                SUM(nvl(cci.defectqty,0)) defectqty,SUM(nvl(cci.reworkqty,0)) reworkqty 
                                FROM stepinfo s
                                LEFT JOIN containercheckinfo2 cci ON cci.workflowstepid = s.workflowstepid AND cci.stepinfoid = s.stepinfoid AND cci.isuse = 0 AND cci.containerid = '{0}' 
                                LEFT JOIN CONTAINERSTEPWORKREPORT2 csw ON csw.CONTAINERSTEPWORKREPORTID = cci.CONTAINERSTEPWORKREPORTID and csw.isuse = 0
                                WHERE s.workflowstepid = '{1}'
                                AND s.revision = (SELECT s1.revision FROM containerstepworkreport2 csw1 
                                LEFT JOIN stepinfo s1 ON s1.stepinfoid = csw1.stepinfoid
                                WHERE csw1.containerid = '{0}' AND csw1.workflowstepid = '{1}' AND csw1.isuse = 0 AND ROWNUM = 1)
                                GROUP BY s.stepinfoid
                              </string>
        strSql = String.Format(strSql, para("ContainerId").ToString, para("WorkflowStepID").ToString)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取可撤销的工步检验 add by zhangrj 2022-09-23
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoDeletableCheckInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetStepInfoDeletableCheckInfo
        Dim strSql = <string>
 SELECT cc.containercheckinfoid,cci.containername,cci.containerid,cci.containertype,cci.qty wipqty, s.description StepInfoDesc, cc.checkqty checkqty,nvl(cc.lossqty,0) lossqty,
e.fullname CheckerName,ws.workflowstepname,ws.workflowstepid,to_char(cc.createdate,'yyyy-MM-dd') checkdate,csr.checktype
,s.stepinfoid,s.stepseq,csr.containerstepworkreportid
FROM containercurrentinfo cci 
LEFT JOIN Workflowstep ws ON ws.workflowstepid = cci.workflowstepid
LEFT JOIN stepinfo s ON s.workflowstepid = ws.workflowstepid
LEFT JOIN containercheckinfo2 cc ON cc.containerid = cci.containerid AND cc.workflowstepid = ws.workflowstepid AND cc.stepinfoid = s.stepinfoid AND cc.isuse = 0
LEFT JOIN containerstepworkreport2 csr ON csr.containerstepworkreportid = cc.containerstepworkreportid AND csr.isuse = 0
LEFT JOIN employee e ON e.employeeid = cc.checkerid
WHERE cc.checkerid = '{0}' AND cci.factoryid = '{1}'
AND cci.isinstore = 0 AND cci.containerstatus = 1
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = cci.containerid)
                        </string>.Value
        strSql = String.Format(strSql, para("CheckerID").ToString, para("FactoryID").ToString)

        If Not String.IsNullOrWhiteSpace(para("ScanContainerName").ToString) Then
            strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
            strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
        End If
        strSql += " ORDER BY s.stepseq"
        Dim result As New uMESProcessResult
        result.Result = True
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return result
    End Function

    ''' <summary>
    ''' 保存工步检验撤销
    ''' </summary>
    ''' <param name="dtCheck"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveStepInfoCheckCancel(ByVal dtCheck As DataTable) As uMESProcessResult Implements uLMSCheckIBusiness.SaveStepInfoCheckCancel
        Dim result As New uMESProcessResult
        Try

            If m_ContainerObj.IsCombinateContainer(dtCheck.Rows(0)("ContainerID").ToString) Then
                result.Result = False
                result.Message = "批次已合卡，不可撤销"
                Return result
                Exit Function
            End If

            Dim sqlList As New ArrayList
            '验证是否存在更高顺序号工步
            Dim strSql = <string>
SELECT cci.containercheckinfoid FROM containercheckinfo2 cci 
LEFT JOIN stepinfo s ON s.stepinfoid = cci.stepinfoid
WHERE cci.containerid = '{0}' AND cci.workflowstepid = '{1}' AND cci.isuse = 0 AND s.stepseq > {2}
                         </string>.Value
            strSql = String.Format(strSql, dtCheck.Rows(0)("ContainerID").ToString, dtCheck.Rows(0)("workflowstepID").ToString, dtCheck.Rows(0)("StepSeq").ToString)
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            If dt.Rows.Count > 0 Then '如果存在已经检验的更高顺序号工步，不能撤销
                result.Result = False
                result.Message = "批次检验已存在更高的顺序号工步，不能跨工步撤销"
                Return result
                Exit Function
            End If
            '            '验证是否存在报废
            '            strSql = <string>
            'SELECT SUM(nvl(cci.checkqty,0)) checkqty,SUM(nvl(cci.lossqty,0)) lossqty FROM containercheckinfo2 cci 
            'WHERE cci.stepinfoid = '{0}' AND cci.workflowstepid = '{1}' AND cci.containerid = '{2}' AND cci.isuse = 0
            'GROUP BY cci.stepinfoid
            '                     </string>.Value
            '            strSql = String.Format(strSql, dtCheck.Rows(0)("StepInfoID").ToString, dtCheck.Rows(0)("workflowstepID").ToString, dtCheck.Rows(0)("ContainerID").ToString)
            '            Dim dt1 As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
            '            Dim checkQty = Integer.Parse(dt1.Rows(0)("checkqty").ToString)
            Dim lossQty = 0
            '撤销检验
            For Each row As DataRow In dtCheck.Rows
                strSql = "UPDATE containercheckinfo2 cci SET cci.isuse = 1 WHERE cci.containercheckinfoid = '" + row("ContainerCheckInfoId").ToString + "'"
                If sqlList.Contains(strSql) Then '防止重复数据
                    Continue For
                End If
                sqlList.Add(strSql)
                ''如果撤销的是首检，则更改批次首检标识
                'If row("checktype").ToString = "1" Then
                '    strSql = "UPDATE containercurrentinfo c SET c.firstchecknum = 1 WHERE c.containerid = '" + row("ContainerID").ToString + "'"
                '    sqlList.Add(strSql)
                'ElseIf row("checktype").ToString = "2" Then  '如果撤销的是序检，则更改批次序检标识
                '    strSql = "UPDATE containercurrentinfo c SET c.stepchecknum = 1 WHERE c.containerid = '" + row("ContainerID").ToString + "'"
                '    sqlList.Add(strSql)
                'End If
                '如果该检验存在报废，更新对应报工的报废数
                If Integer.Parse(row("lossqty").ToString) > 0 Then
                    '更新批次表报废数
                    strSql = "UPDATE containercurrentinfo c SET c.alllossqty = NVL(c.alllossqty,0) - {0} WHERE c.containerid = '{1}'"
                    strSql = String.Format(strSql, Integer.Parse(row("lossqty").ToString), row("ContainerID").ToString)
                    sqlList.Add(strSql)
                    '更新报工表报废数
                    strSql = "UPDATE containerstepworkreport2 csw SET csw.lossqty = NVL(csw.lossqty,0) - {0} WHERE csw.containerstepworkreportid = '{1}'"
                    strSql = String.Format(strSql, Integer.Parse(row("lossqty").ToString), row("ContainerStepWorkReportID").ToString)
                    sqlList.Add(strSql)
                    '撤销工步检验报废记录表
                    strSql = "select ch.checkqtychangerecordid,ch.containerid,ch.qty from checkqtychangerecord ch where ch.containercheckinfoid='{0}' and ch.stepinfoid = '{1}' and ch.isuse = 0"
                    strSql = String.Format(strSql, row("ContainerCheckInfoId").ToString, dtCheck.Rows(0)("StepInfoID").ToString)
                    Dim dtChange As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
                    For Each item As DataRow In dtChange.Rows
                        strSql = "UPDATE checkqtychangerecord ccr SET ccr.isuse = 1 WHERE ccr.checkqtychangerecordid = '" + item("checkqtychangerecordid").ToString + "'"
                        sqlList.Add(strSql)
                        If row("ContainerType").ToString = "3" Then '如果是子序号的，更新子序号批次状态和数量
                            strSql = "update container c1 set c1.status=1,c1.qty=c1.qty+({0}) where c1.containerid='{1}'"
                            strSql = String.Format(strSql, Math.Abs(Integer.Parse(item("qty").ToString)), item("containerid").ToString)
                            sqlList.Add(strSql)
                        End If
                    Next
                    lossQty += Integer.Parse(row("lossqty").ToString)
                End If
            Next
            '撤销工序检验记录
            strSql = "UPDATE containercheckinfo cci1 SET cci1.isuse = 1 WHERE cci1.containerid = '{0}' AND cci1.workflowstepid = '{1}' AND cci1.isuse = 0"
            strSql = String.Format(strSql, dtCheck.Rows(0)("ContainerID").ToString, dtCheck.Rows(0)("workflowstepID").ToString)
            sqlList.Add(strSql)
            DatabaseHelper.ExecuteSqlTran(sqlList)

            '如果有报废数，则要更改批次在制数
            If lossQty > 0 Then
                m_ContainerObj.ChangeContainerQty(dtCheck.Rows(0)("ContainerID").ToString, lossQty)
            End If

            result.Result = True
            result.Message = "保存成功"
            Return result
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
            Return result
        End Try
    End Function

    ''' <summary>
    ''' 获取批量检验工步数据（未启用）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetStepInfoBatchCheckList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetStepInfoBatchCheckList
        Dim strSelect As String =
            <string>
SELECT
cci.containerid,cci.containername,cci.containertype,cci.productid,cci.workflowid,s.stepno,s.stepinfoid,s.description stepinfoname,s.stepseq,cci.firstchecknum,cci.stepchecknum,
cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containerno,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid,
DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,csr.createdate,cci.workflowstepsequence AS sequence
FROM containerstepworkreport2 csr 
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
LEFT JOIN containerstepnoship csn ON csn.containerid=cci.containerid AND csn.workflowstepid=cci.workflowstepid AND csn.isuse=0 AND csn.noship=1 
LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
LEFT JOIN employee e ON e.employeeid = csr.workerid
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 
AND csr.workflowstepid = cci.workflowstepid --当前序下的工步
AND (CASE WHEN cci.Originalfactoryid = '{0}' and csn.containerstepnoshipid is null THEN cci.SPECFACTORYID ELSE '{0}' END)= '{0}'
AND (CASE WHEN cci.Originalfactoryid {1} '{0}' and csn.containerstepnoshipid is null THEN cci.SHIPNUM ELSE 5 END)>4
AND cci.factoryid = '{0}'
            </string>.Value
        strSelect = String.Format(strSelect, para("FactoryID").ToString, "<>")

        If Not String.IsNullOrWhiteSpace(para("OrderName")) Then
            strSelect += " AND cci.mfgordername like '%" + para("OrderName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
            strSelect += " AND cci.productname like '%" + para("ProductName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
            strSelect += " AND cci.containername LIKE '%" + para("ContainerName") + "%'"
        End If

        'If para.Keys.Contains("CheckerID") Then
        '    If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
        '        strSelect += " AND csr.CheckerID = '" + para("CheckerID") + "'"
        '    End If
        'End If
        strSelect += " ORDER BY cci.containerid,s.stepseq"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取批量检验工步数据（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRBStepInfoBatchCheckList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetRBStepInfoBatchCheckList
        Dim strSelect1 As String =
            <string>
SELECT cci.containerid,cci.containername,cci.containertype,cci.productid,cci.workflowid,s.stepno,s.stepinfoid,s.description stepinfoname,s.stepseq,cci.firstchecknum,cci.stepchecknum,
cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.qty AS wipqty,csr.qty,e.fullname employeename,csr.checktype,csr.containerstepworkreportid,
DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,csr.createdate,cci.workflowstepsequence AS sequence
FROM containerstepworkreport2 csr 
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
LEFT JOIN Containercurrentinfo cci ON cci.containerid = csr.containerid
LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
LEFT JOIN employee e ON e.employeeid = csr.workerid
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 
AND csr.workflowstepid = cci.workflowstepid --当前序下的工步
AND cci.factoryid = '{0}' AND NOT EXISTS (SELECT 1 FROM containercombinate cb WHERE cb.containerid = csr.containerid)
{1}
            </string>.Value
        Dim strSelect2 As String = <string>
SELECT '' containerid,cb.containercombinatename containername,cci.containertype,cci.productid,cci.workflowid,s.stepno,s.stepinfoid,s.description stepinfoname,s.stepseq,NULL firstchecknum,NULL stepchecknum,
cci.workflowstepname,cci.workflowstepid,cci.factoryid,sum(cci.qty) wipqty,sum(csr.qty) qty,e.fullname employeename,NULL checktype,'' containerstepworkreportid,
DECODE(cci.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,max(csr.createdate) createdate,cci.workflowstepsequence AS sequence
FROM containercombinate cb
LEFT JOIN Containercurrentinfo cci ON cci.containerid = cb.containerid
LEFT JOIN containerstepworkreport2 csr ON csr.containerid = cci.containerid AND csr.workflowstepid = cci.workflowstepid
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csr.containerstepworkreportid AND cc.isuse = 0
LEFT JOIN stepinfo s ON s.workflowstepid = csr.workflowstepid AND s.stepinfoid = csr.stepinfoid
LEFT JOIN employee e ON e.employeeid = csr.workerid
WHERE csr.isuse = 0 AND cc.containercheckinfoid IS NULL
AND cci.isinstore in (-1,0) AND cci.containerstatus=1 AND cci.factoryid = '{0}'
{1}
GROUP BY cb.containercombinatename,cci.containertype,cci.productid,cci.workflowid,s.stepno,s.stepinfoid,s.description,s.stepseq,e.fullname,
cci.workflowstepname,cci.workflowstepid,cci.factoryid,cci.containertype,cci.workflowstepsequence
                                   </string>.Value
        Dim strWhere = String.Empty
        Dim strSelect = String.Format(<string>
                                          {0}
                                          UNION ALL
                                          {1}
                                      </string>.Value, strSelect1, strSelect2)
        If para.Keys.Contains("CombinateName") Then
            If Not String.IsNullOrWhiteSpace(para("CombinateName")) Then
                strSelect = strSelect2
                strWhere += " AND cb.containercombinatename like '%" + para("CombinateName") + "%'"
            End If
        End If

        '通用查询
        If Not String.IsNullOrWhiteSpace(para("OrderName")) Then
            strWhere += " AND cci.mfgordername like '%" + para("OrderName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
            strWhere += " AND cci.productname like '%" + para("ProductName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
            strWhere += " AND cci.containername LIKE '%" + para("ContainerName") + "%'"
        End If

        'If para.Keys.Contains("CheckerID") Then
        '    If Not String.IsNullOrWhiteSpace(para("CheckerID")) Then
        '        strSelect += " AND csr.CheckerID = '" + para("CheckerID") + "'"
        '    End If
        'End If
        strSelect = String.Format(strSelect, para("FactoryID").ToString, strWhere)
        strSelect = String.Format("select * from ({0})k order by k.containername,k.workflowstepid,k.stepseq", strSelect)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 工步批量检验前验证 add by zhangrj 2022-10-14
    ''' </summary>
    ''' <param name="row"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsValidBatchStepInfoCheck(ByVal row As DataRow) As Boolean Implements uLMSCheckIBusiness.IsValidBatchStepInfoCheck
        Dim strSql = <string>
                        SELECT SUM(NVL(csw.qty,0)) reportqty,cci.qty FROM stepinfo s
                        LEFT JOIN containerstepworkreport2 csw ON csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.stepinfoid = s.stepinfoid AND csw.isuse = 0
                        LEFT JOIN containercurrentinfo cci ON cci.containerid = '{0}' AND cci.workflowstepid = s.workflowstepid
                        WHERE s.workflowstepid = '{1}' AND s.stepseq {3} '{2}' 
                        AND s.revision = (SELECT s1.revision FROM stepinfo s1 WHERE s1.stepinfoid = '{4}')
                        GROUP BY s.stepinfoid,cci.qty
                     </string>.Value
        '查询该工步顺序号之前的工步是否完整报工
        strSql = String.Format(strSql, row("ContainerID").ToString(), row("WorkflowstepID").ToString(), row("StepSeq").ToString(), "<", row("StepInfoID").ToString())
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        If dt.Rows.Count = 0 Then
            Return True
        End If

        Dim wipqty = Integer.Parse(dt.Rows(0)("qty").ToString())

        '如果存在工步报工数小于总数，则表示没有报工完
        If dt.AsEnumerable().Where(Function(p) Integer.Parse(p("reportqty").ToString()) < wipqty).Count > 0 Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 工步检验确认前验证是否存在无效的报工 add by zhangrj 2023-04-20
    ''' </summary>
    ''' <param name="workReportIDList"></param>
    ''' <param name="type">0：多ID查询，1：单ID查询</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildWorkReportForStepInfoCheck(ByVal workReportIDList As String, ByVal type As Integer) As Boolean Implements uLMSCheckIBusiness.IsVaildWorkReportForStepInfoCheck
        workReportIDList = workReportIDList.Replace(",", "','")
        Dim strSql = "SELECT csw.isuse FROM containerstepworkreport2 csw "
        If type = 0 Then '多个ID查询
            strSql += String.Format(" WHERE csw.containerstepworkreportid IN ('{0}')", workReportIDList)
        ElseIf type = 1 Then '单个ID查询
            strSql += String.Format(" WHERE csw.containerstepworkreportid = '{0}'", workReportIDList)
        End If
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Select("isuse = 1").Length > 0 Then
            Return False
        End If
        Return True
    End Function
#End Region

    ''' <summary>
    ''' yuxj20220923判断是否是施工单
    ''' </summary>
    ''' <param name="strCid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function boolIsSGD(ByVal strCid As String) As Boolean Implements uLMSCheckIBusiness.boolIsSGD
        boolIsSGD = False
        Dim strsql As String =
            String.Format("select nvl(ep.workflowstepid,'0') workflowstepid from enginepartplan ep where ep.containerid='{0}' or ep.enginecontainerid='{0}'", strCid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If (dt.Rows.Count > 0) Then
            If dt.Rows(0)("workflowstepid").ToString = "0" Then
                boolIsSGD = True
            End If
        End If
    End Function

#Region "批次工序检验，外委，转工完成，移动下序整合统一方法"

    ''' <summary>
    ''' 移动到下序，统一方法
    ''' add:Wangjh 20221029
    ''' </summary>
    ''' <param name="conPara"></param>
    ''' <param name="host"></param>
    ''' <param name="LastWipQty"></param>
    ''' <param name="levelAttributes"></param>
    ''' <param name="userInfo"></param>
    ''' <param name="strMsg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MoveNextStep(ByVal conPara As Dictionary(Of String, String), ByVal host As ServerHostInfo, ByVal LastWipQty As Integer, ByVal levelAttributes As DataTable, ByVal userInfo As CurrentUserInfo, ByRef strMsg As String) As Boolean Implements uLMSCheckIBusiness.MoveNextStep
        '判断检验的当前工序是否是转工的最后一道工序 create：Wangjh 2017-6-17
        Dim isShipPara As New Dictionary(Of String, String)
        isShipPara("ToFactoryID") = userInfo.FactoryID
        isShipPara("ContainerID") = conPara("ContainerID")
        isShipPara("WorkFlowStepID") = conPara("WorkflowStepID")
        If IsLastShipStep(isShipPara) Then
            MoveNextStep = True
            Exit Function
        End If

        '下序相关信息
        Dim nextPara As Dictionary(Of String, String) = New Dictionary(Of String, String)
        nextPara("WorkFlowID") = conPara("WorkflowID").ToString
        nextPara("Sequence") = Val(conPara("Sequence").ToString) + 1
        nextPara("ContainerID") = conPara("ContainerID").ToString
        nextPara("FactoryID") = userInfo.FactoryID

        '获取后面第一个未完工的序 add:Wangjh 20180830
        Dim noComStep = m_CommonObj.GetContainerNoCompleteStep(nextPara)
        If isShipPara.ContainsKey("Out_ShipLastSequence") Then '处于转工流程，并需要移动的情况 add:wangjh 20220624
            If Val(noComStep.Rows(0)("Sequence")) > Val(isShipPara("Out_ShipLastSequence")) Then '系统转移的下序>转工最后一序情况

                Dim exuteEntity = New ExcuteEntity("workflowstep", ExcuteEntity.ExcuteTypeEnum.select_)
                exuteEntity.WhereFileds = New List(Of FieldEntity) From {New FieldEntity("workflowstepid", isShipPara("Out_ShipLastStepID"), FieldEntity.FieldTypeEnum.Str)}

                exuteEntity.ExcuteFileds = New List(Of FieldEntity) From {New FieldEntity("workflowstepname", Nothing, FieldEntity.FieldTypeEnum.Str),
                                                                          New FieldEntity("sequence", Nothing, FieldEntity.FieldTypeEnum.Str),
                                                                          New FieldEntity("workflowstepid", Nothing, FieldEntity.FieldTypeEnum.Str),
                                                                          New FieldEntity("controlcode", Nothing, FieldEntity.FieldTypeEnum.Str),
                                                                          New FieldEntity("wipmsglabel", Nothing, FieldEntity.FieldTypeEnum.Str)}

                Dim lastShipDt = DatabaseHelper.QueryDataByEntity(exuteEntity)

                If lastShipDt.Rows.Count > 0 Then
                    noComStep.Rows(0)("workflowstepname") = lastShipDt.Rows(0)("workflowstepname")
                    noComStep.Rows(0)("workflowstepid") = lastShipDt.Rows(0)("workflowstepid")
                    noComStep.Rows(0)("sequence") = lastShipDt.Rows(0)("sequence")
                    noComStep.Rows(0)("controlcode") = lastShipDt.Rows(0)("controlcode")
                    noComStep.Rows(0)("wipmsglabel") = lastShipDt.Rows(0)("wipmsglabel")
                    noComStep.AcceptChanges()
                End If
            End If
        End If
        '移动工序
        Dim datarows() As DataRow = levelAttributes.Select("USERATTRIBUTENAME='入库流程'")
        Dim strWF As String = ""
        Dim strWFRev As String = ""
        If datarows.Length > 0 Then
            Dim strWorkflows() = datarows(0)("ATTRIBUTEVALUE").ToString.Split("：")
            strWF = strWorkflows.First
            strWFRev = strWorkflows.Last
        End If

        datarows = levelAttributes.Select("USERATTRIBUTENAME='入库工序'")
        Dim strStep As String = ""
        If datarows.Length > 0 Then
            strStep = datarows.First.Item("ATTRIBUTEVALUE")
        End If

        Dim isSubCon = False '是否是子卡
        If conPara("ContainerName").ToString.Contains("子工艺") Or m_CommonObj.GetTableInfo("containersubcontainer", "subcontainerid", conPara("ContainerID").ToString).Rows.Count > 0 Then
            isSubCon = True
            '子卡检验完毕，检查父卡自动报工
            MainContainerAutoWork(conPara, userInfo)
        End If

        '检查是否需要军检，需要提交军检的不移动 add:wangjh 20221223
        If CheckContainerStepIsArmy(conPara("ContainerID"), conPara("WorkflowStepID"), "1").Rows.Count > 0 Then
            strMsg = "此件号此工序需要军检，请等待军检结果反馈后进行下序"
            LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName") + "@^" + "工序：" + conPara("WorkflowStepID") + strMsg + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
            Return True
        End If
        '

        Dim excResult = MoveContainer(conPara, noComStep, host, strWF, strWFRev, strStep, LastWipQty, userInfo, strMsg)
        'LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName") + "@^" + "工序：" + conPara("WorkflowStepID") + strMsg + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
        If excResult = False Then
            Return False
        End If

        '若当前卡为子卡，检查主卡当前序对应的子工艺序是否全部干完，若全部干完则自动移动(注意需要排除主卡正在转工最后序的情况) add:Wangjh 20181205
        If isSubCon Then
            '两种情况父卡需要联动1:子卡入库 2：主工序对应的子工序干完eg:主工序10序对应子工序5,10。情况2交给后台服务
            If noComStep.Rows.Count = 0 Then '增加判断-干完了入库情况，检查是否需要父卡联动 add:Wangjh 20220420
                '检查子卡所有序是否已干完
                Dim checkPara As New Dictionary(Of String, String)
                checkPara.Add("WorkflowStepID", conPara("WorkflowStepID").ToString)
                checkPara.Add("ContainerID", conPara("ContainerID").ToString)
                nextPara.Clear()
                Dim resMsg As String = ""
                nextPara = m_CommonObj.CheckFaContainerAndReturnMoveInfo(checkPara, resMsg)
                If Not String.IsNullOrWhiteSpace(resMsg) Then
                    LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName").ToString + "检查主卡移动" + "@^" + resMsg + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
                End If
                If nextPara IsNot Nothing Then
                    noComStep = m_CommonObj.GetContainerNoCompleteStep(nextPara)
                    MoveContainer(nextPara, noComStep, host, strWF, strWFRev, strStep, Val(nextPara("WipQty")), userInfo, strMsg)
                    LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName") + "检查主卡移动" + "@^" + "主卡:" + nextPara("ContainerName") + strMsg + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
                End If
                '
            End If
        End If

        MoveNextStep = True
    End Function

    ''' <summary>
    ''' 判读军检标识
    ''' add:Wangjh 20221223
    ''' </summary>
    ''' <param name="containerId"></param>
    ''' <param name="stepID"></param>
    ''' <param name="armyType">1:工序军检 2：成品入库军检</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckContainerStepIsArmy(ByVal containerId As String, ByVal stepID As String, ByVal armyType As String) As DataTable
        Dim strSql = "select productname from containercurrentinfo where containerId='" + containerId + "'"
        Dim productName = DatabaseHelper.GetSingle(strSql)

        If productName Is Nothing Then
            Return New DataTable
        End If

        Dim armychecktype = ""
        Dim validateSql = "select ad.* from ArmyCheckDirInfo ad where ad.isuse=0 and ad.armyyear=" + Date.Now.Year.ToString
        validateSql += String.Format(" and ad.partname='{0}' ", productName.ToString)
        Dim result As New DataTable
        If armyType = "1" Then
            armychecktype = "工序军检"

            strSql = <string>select w.process_no,s.specno from workflowstep ws
left join workflow w on w.workflowid=ws.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)
where ws.workflowstepid='{0}'</string>.Value

            Dim stepDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, stepID))
            If stepDt.Rows.Count = 0 Then
                Return New DataTable
            End If

            validateSql += String.Format(" and ad.armychecktype='{0}' and ad.specno='{1}' and ad.processno='{2}' ", armychecktype, stepDt.Rows(0)("specno").ToString, stepDt.Rows(0)("process_no").ToString)

            result = DatabaseHelper.Query_ReturnDatTable(validateSql)
            If result.Rows.Count > 0 Then
                '如果是，需要更新检验表标识
                Dim updateSql = <string>begin
  update containercheckinfo cci set cci.IsArmyCheck=1 where cci.containerid='{0}' and cci.workflowstepid='{1}' and cci.isuse=0;
  update containerfinishedcheckinfo cfci set cfci.isarmycheck=1 where cfci.containerid='{0}' and cfci.workflowstepid='{1}' and cfci.isuse=0;
  commit;
end;</string>.Value
                DatabaseHelper.ExecuteSql(String.Format(updateSql, containerId, stepID))
            End If
        ElseIf armyType = "2" Then
            armychecktype = "成品军检"

            validateSql += String.Format(" and ad.armychecktype='{0}' ", armychecktype)

            result = DatabaseHelper.Query_ReturnDatTable(validateSql)
        End If

        Return result
    End Function

    ''' <summary>
    ''' 移动批次
    ''' </summary>
    ''' <param name="conPara">批次一些信息</param>
    ''' <param name="noComStep"></param>
    ''' <param name="host"></param>
    ''' <param name="strWF">入库工艺</param>
    ''' <param name="strWFRev">入库工艺版本</param>
    ''' <param name="strStep">入库工序</param>
    ''' <param name="LastWipQty">最终合格数</param>
    ''' <param name="userInfo"></param>
    ''' <param name="strMsg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function MoveContainer(ByVal conPara As Dictionary(Of String, String), ByVal noComStep As DataTable, ByVal host As ServerHostInfo, ByVal strWF As String, ByVal strWFRev As String, ByVal strStep As String, ByVal LastWipQty As Integer, ByVal userInfo As CurrentUserInfo, ByRef strMsg As String) As Boolean
        Dim containerName = conPara("ContainerName")
        '移动批次
        If noComStep.Rows.Count = 0 Then '干完入库情况，将当前批次的工艺保存下来
            MoveContainer = m_ContainerObj.ContainerMoveNonStd(host, containerName, strWF, strWFRev, strStep, userInfo.EmployeeName, "", strMsg)
            If MoveContainer Then
                Dim exuteEntity = New ExcuteEntity("containercurrentinfo", ExcuteEntity.ExcuteTypeEnum.update)
                Dim excuteFieldList As New List(Of FieldEntity)

                excuteFieldList.Add(New FieldEntity("bsworkflowid", conPara("WorkflowID").ToString, FieldEntity.FieldTypeEnum.Str)) '保存入库前的工艺规程
                exuteEntity.ExcuteFileds = excuteFieldList

                exuteEntity.WhereFileds = New List(Of FieldEntity) From {New FieldEntity("containerid", conPara("ContainerID").ToString, FieldEntity.FieldTypeEnum.Str)}

                DatabaseHelper.ExecuteDataByEntity(exuteEntity)
            End If
        Else
            '非标移动到转工最后序后面第一个未完工的序
            MoveContainer = m_ContainerObj.ContainerMoveNonStd(host, noComStep.Rows(0)("ContainerName").ToString, noComStep.Rows(0)("WORKFLOWNAME").ToString, noComStep.Rows(0)("WORKFLOWREVISION").ToString, noComStep.Rows(0)("workflowstepname").ToString, userInfo.EmployeeName, noComStep.Rows(0)("workflowstepID").ToString, strMsg)
            If MoveContainer And noComStep.Rows(0)("controlcode").ToString = "LY05" Then '固定外委
                Dim newpara As New Dictionary(Of String, String)
                newpara.Add("WorkflowID", noComStep.Rows(0)("WorkflowID").ToString)
                newpara.Add("Sequence", noComStep.Rows(0)("Sequence").ToString)
                newpara.Add("WorkflowStepID", noComStep.Rows(0)("WorkflowStepID").ToString)
                newpara.Add("FactoryID", userInfo.FactoryID)
                newpara.Add("EmployeeID", userInfo.EmployeeID)
                newpara.Add("OutQty", LastWipQty)
                newpara.Add("ContainerID", noComStep.Rows(0)("ContainerID").ToString)
                newpara.Add("ContainerName", noComStep.Rows(0)("ContainerName").ToString)
                m_Commission.AddFixedCommissionInfo(newpara)
            End If
            If MoveContainer Then
                Dim dic As New Dictionary(Of String, String)
                dic.Add("ContainerID", conPara("ContainerID").ToString)
                dic.Add("ContainerName", conPara("ContainerName").ToString)
                dic.Add("ProductId", "")
                dic.Add("ProductName", "")
                dic.Add("WorkflowID", conPara("WorkflowID").ToString)
                dic.Add("EmployeeID", userInfo.EmployeeID.ToString)
                dic.Add("WorkflowStepID", noComStep.Rows(0)("workflowstepid").ToString)
                dic.Add("Qty", "")
                m_ContainerObj.UpdateParallelStepContainerHold(dic) '是否平行作业工序暂停 add by zhangrj 
                'm_ContainerObj.UpdateSpecialStepContainerHold(dic) '是否特殊过程工序暂停 add by zhangrj 20220908
            End If
        End If
    End Function

    ''' <summary>
    ''' 子卡检验完毕，检查主卡自动报工
    ''' add:Wangjh 2022/11/22
    ''' </summary>
    ''' <param name="conPara"></param>
    ''' <param name="userInfo"></param>
    ''' <param name="isAheadFinish">是否提前需要主卡自动报工</param>
    ''' <remarks></remarks>
    Public Sub MainContainerAutoWork(ByVal conPara As Dictionary(Of String, String), ByVal userInfo As CurrentUserInfo, Optional ByVal isAheadFinish As Boolean = False) Implements uLMSCheckIBusiness.MainContainerAutoWork
        '当前卡为子卡，且是主卡工序对应的最后子工序，检查主卡是否在对应工序上，若在执行自动报工。add:Wangjh 20221115 eg:主卡10序，对应子卡只是5,10序
        Dim strSql = <stirng>select sc.containertype soncontainertype,fc2.qualificationnum faqualificationnum,fc.containertype facontainertype,
fc.containername facontainername,fc.containerid facontainerid,fc.qty faqty,null sonworkflowid,fc.workflowstepid facurstepid,
fc.workflowstepid fastepid,null maxsonstepid,null maxsonstepname,sc.workflowname subworkflowname,sc.workflowstepname subcurstepname  from containersubcontainer cc
left join containercurrentinfo fc on fc.containerid=cc.containerid--父卡
left join container fc2 on fc2.containerid=fc.containerid--父卡2
left join containercurrentinfo sc on sc.containerid=cc.subcontainerid--子卡
where cc.subcontainerid='{0}'
group by sc.containertype,fc.containertype,fc2.qualificationnum,fc.containername,fc.containerid,fc.workflowstepid,fc.qty,sc.workflowname,sc.workflowstepname
</stirng>.Value

        Dim faConDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conPara("ContainerID").ToString)) ', conPara("WorkflowID"), userInfo.FactoryID
        If faConDt.Rows.Count = 0 Then
            LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName").ToString + "检查主卡报工" + "@^" + "未找此子工艺到对应的主卡" + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
            Return
        Else
            Dim subWorkflowName = faConDt.Rows(0)("subworkflowname").ToString '子卡当前工艺规程
            Dim subcurstepname = faConDt.Rows(0)("subcurstepname").ToString '子卡当前工序

            '查询子卡信息
            strSql = <string>select ss.workflowid,ss.sonworkflowid,ss.workflowstepid,ss.sonworkflowstepid,ws.workflowstepname sonworkflowstepname  from stepsubstep ss 
left join workflowstep ws on ws.workflowstepid=ss.sonworkflowstepid
left join workflow sw on sw.workflowid=ss.sonworkflowid
left join workflowbase swb on swb.workflowbaseid=sw.workflowbaseid
where swb.workflowname='{0}' and ss.workflowstepid='{1}'--ss.sonworkflowid='{0}'
union all
select sbs.workflowid,subw.workflowid sonworkflowid,sbs.workflowstepid,subws.workflowstepid sonworkflowstepid,subws.workflowstepname sonworkflowstepname 
from (
SELECT w.workflowid,w.productid  FROM workflow w
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
WHERE w.factoryid='{2}'
GROUP BY w.workflowid,w.productid
having count(ws.workflowstepid)=1  
) w--通用工艺情况下，第二层虚拟子工艺 
LEFT JOIN workflowstep ws ON ws.workflowid=w.workflowid
left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid)        
INNER join workflow subw on subw.process_no=s.commonsubworkflow--通用孙子工艺
inner join workflowbase swb on swb.revofrcdid=subw.workflowid
left join workflowstep subws on subws.workflowid=subw.workflowid--通用孙工序
LEFT JOIN stepsubstep sbs ON sbs.sonworkflowid=w.workflowid--第二层虚拟子工艺的关联信息
WHERE   NVL2(s.commonsubworkflow,'1','0')='1' and swb.workflowname='{0}' --subw.workflowid='{0}'
and sbs.workflowstepid='{1}'</string>.Value

            Dim sonStepDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, subWorkflowName, faConDt.Rows(0)("facurstepid").ToString, userInfo.FactoryID))
            If sonStepDt.Rows.Count = 0 Then
                LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName").ToString + "检查主卡报工" + "@^" + "此子卡的父工艺信息未找到" + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
                Return
            End If

            Dim sonworkflowstepid = sonStepDt.Compute("max(sonworkflowstepid)", "")
            For Each faRow In faConDt.Rows
                faRow("maxsonstepid") = sonworkflowstepid
                faRow("maxsonstepname") = sonStepDt.Select("sonworkflowstepid='" + sonworkflowstepid + "'")(0)("sonworkflowstepname").ToString
                faRow("sonworkflowid") = sonStepDt.Rows(0)("sonworkflowid")
            Next

            Dim sonStepNames = ""
            For Each sonStep As DataRow In sonStepDt.Rows
                sonStepNames += "'" + sonStep("sonworkflowstepname").ToString + "',"
            Next
            sonStepNames = sonStepNames.Trim(",")

            If faConDt.Rows(0)("maxsonstepname").ToString <> subcurstepname And isAheadFinish = False Then 'conPara("WorkflowStepID") 
                LogHelp.WriteOrcale("检验日志记录" + "@^" + "批次：" + conPara("ContainerName").ToString + "检查主卡报工" + "@^" + "此子卡未在主工序对应的最后子工序，不自动报工主卡" + "@^" + Convert.ToString(Guid.NewGuid()) + "@^" + userInfo.FullName) 'add:Wangjh 写入数据库   
                Return
            End If
            Dim sqlList As New ArrayList
            '主卡开始自动报工
            Dim autoReportSql = <string>INSERT INTO containerstepworkreport csw (csw.containerstepworkreportid,csw.containerid,csw.workflowstepid,csw.isuse,csw.checktype,csw.workerid,
csw.qty,csw.selfcheckqualifiedqty,csw.selfcheckoutoftoleranceqty,csw.selfchecklossqty,csw.createdate,csw.lossqty,factoryid) VALUES('{0}','{1}','{2}',0,30,'{3}','{4}','{5}','{6}','{7}',sysdate,'{8}','{9}')</string>.Value
            Dim autoCheckSql = <string>INSERT INTO containercheckinfo cci (cci.containercheckinfoid,cci.containerstepworkreportid,cci.containerid,cci.workflowstepid,cci.checkerid,cci.checkqty,cci.acceptanceqty,cci.acceptanceqty2,cci.defectqty
,cci.reworkqty,cci.lossqty,cci.checktypenote,cci.createdate,cci.isuse,factoryid) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',1,sysdate,0,'{11}')</string>.Value
            Dim lossSql = <string>INSERT INTO CheckQtyChangeRecord(Checkqtychangerecordid,Containercheckinfoid,Containerid,Changeqtytype,Isuse,Qty,Reasonid,Scrapsourcetype,Containerstepworkreportid,Createdate,
Employeeid,Workflowstepid)VALUES('{0}','{1}','{2}',2,0,'{3}','子卡干完主卡自动报废' ,1,'{4}',Sysdate,'{5}','{6}') </string>.Value
            '取子卡报工数据
            strSql = <string>select cci.containerid,cci.workflowstepid,sum(cci.qty) reportqty,sum(nvl(cci.LossQty,0)) LossQty,sum(nvl(cci.SelfCheckLossQty,0)) SelfCheckLossQty,
sum(nvl(cci.SelfCheckQualifiedQty,0)) SelfCheckQualifiedQty,sum(nvl(cci.SelfCheckOutOfToleRanceQty,0)) SelfCheckOutOfToleRanceQty,
sum(nvl(cci.SpecimenQty,0)) SpecimenQty,sum(nvl(cci.SpecimenLossQty,0)) SpecimenLossQty,sum(nvl(cci.Qty2,0)) Qty2 
from  containerstepworkreport cci
left join workflowstep ws on ws.workflowstepid=cci.workflowstepid
where cci.containerid='{0}' and cci.isuse=0 and ws.workflowstepid='{1}'
group by cci.containerid,cci.workflowstepid</string>.Value
            Dim sonConReportDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conPara("ContainerID"), conPara("WorkflowStepID")))
            '取子卡检验数据 
            strSql = <string>select cci.containerid,cci.workflowstepid,sum(cci.CheckQty) CheckQty,sum(cci.AcceptanceQty) AcceptanceQty,sum(nvl(cci.AcceptanceQty2,0)) AcceptanceQty2,
sum(nvl(cci.LossQty,0)) LossQty,sum(nvl(cci.ReworkQty,0)) ReworkQty,sum(nvl(cci.DefectQty,0)) DefectQty 
from (
select cci.CheckQty,cci.AcceptanceQty,cci.AcceptanceQty2,cci.LossQty,cci.ReworkQty,cci.DefectQty,cci.workflowstepid,cci.containerid
from containercheckinfo cci where cci.containerid='{0}' and cci.isuse=0
union all
select cci.CheckQty,cci.AcceptanceQty,cci.AcceptanceQty2,cci.LossQty,cci.ReworkQty,cci.DefectQty,cci.workflowstepid,cci.containerid
from containerfinishedcheckinfo cci where cci.containerid='{0}' and cci.isuse=0
) cci
left join workflowstep ws on ws.workflowstepid=cci.workflowstepid
where ws.workflowstepid='{1}'
group by cci.containerid,cci.workflowstepid</string>.Value
            Dim sonConCheckDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conPara("ContainerID"), conPara("WorkflowStepID")))
            '取子卡报废数据
            strSql = <string>select c.containername,c.qualificationnum,ch.changeqtytype,ch.containerid,sum(ch.qty) lossqty from checkqtychangerecord ch 
left join container c on c.containerid=ch.containerid
left join workflowstep ws on ws.workflowstepid=ch.workflowstepid
where (c.parentcontainerid='{0}' or c.containerid='{0}') and ch.isuse=0 and ch.scrapsourcetype in (1,2,3,4,5) --and ws.workflowstepname in ({1})
group by c.containername,c.qualificationnum,ch.changeqtytype,ch.containerid</string>.Value
            Dim sonLossDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conPara("ContainerID"))) ', sonStepNames))
            '取子卡此序的报工人
            strSql = <string>select csw.workerid,csw.createdate from containerstepworkreport csw where csw.containerid='{0}' and csw.workflowstepid='{1}' and csw.isuse=0
order by csw.createdate desc</string>.Value
            Dim sonConReportEmpDt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, conPara("ContainerID"), conPara("WorkflowStepID")))

            '子卡数据赋值
            Dim sonAllLossqty = Val(sonLossDt.Compute("sum(lossqty)", "").ToString())
            If sonAllLossqty < 0 Then
                sonConReportDt.Rows(0)("reportqty") = Val(sonConCheckDt.Rows(0)("AcceptanceQty")) + (-sonAllLossqty)
                sonConReportDt.Rows(0)("LossQty") = -sonAllLossqty

                sonConCheckDt.Rows(0)("CheckQty") = Val(sonConCheckDt.Rows(0)("AcceptanceQty")) + (-sonAllLossqty)
                sonConCheckDt.Rows(0)("AcceptanceQty") = Val(sonConCheckDt.Rows(0)("AcceptanceQty"))
                sonConCheckDt.Rows(0)("AcceptanceQty2") = Val(sonConCheckDt.Rows(0)("AcceptanceQty2"))
                sonConCheckDt.Rows(0)("LossQty") = -sonAllLossqty
            End If

            For Each row As DataRow In faConDt.Rows
                '存在手动报工即跳过
                If DatabaseHelper.GetSingle(String.Format("select 1 from containerstepworkreport where isuse=0 and containerid='{0}' and workflowstepid='{1}' and checktype!=30 ", row("facontainerid").ToString, row("facurstepid").ToString)) IsNot Nothing Then
                    Continue For
                End If
                '主卡自动报工
                Dim reportID = Guid.NewGuid.ToString
                '主卡自动检验
                Dim checkID = Guid.NewGuid.ToString

                Dim mainLossQty = 0, mainStatus = 1 '主卡报废数和状态
                If sonLossDt.Rows.Count > 0 Then '子卡有报废，主卡需自动报废
                    If row("soncontainertype").ToString = "3" Then '子卡是批量带子序号时，主卡可能是单件单卡或批量带子序号
                        If row("facontainertype").ToString = "3" Then
                            Dim faChildDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(<string>select c.containerid,c.containername,c.qualificationnum from container c 
                            where c.parentcontainerid='{0}'</string>.Value, row("facontainerid").ToString))

                            For Each lossRow As DataRow In sonLossDt.Rows
                                Dim faChild = faChildDt.Select("qualificationnum='" + lossRow("qualificationnum").ToString + "'") '找出报废子卡序号对应的主卡序号
                                If faChild.Length > 0 Then
                                    sqlList.Add(String.Format(lossSql, Guid.NewGuid.ToString, checkID, faChild(0)("containerid").ToString, -1, reportID, userInfo.EmployeeID, row("facurstepid").ToString))
                                    sqlList.Add(String.Format("update container c set status=2,qty=0 where containerid='{0}'", faChild(0)("containerid").ToString)) '主卡对应的子序号报废
                                    mainLossQty -= 1
                                End If
                            Next

                        ElseIf row("facontainertype").ToString = "7" Then
                            If sonLossDt.Select("qualificationnum='" + row("faqualificationnum").ToString + "'").Length > 0 Then '存在此子序号对应的报废数据
                                sqlList.Add(String.Format(lossSql, Guid.NewGuid.ToString, checkID, row("facontainerid").ToString, -1, reportID, userInfo.EmployeeID, row("facurstepid").ToString))
                                mainLossQty -= 1
                            End If
                        End If
                    Else
                        mainLossQty = Val(sonLossDt.Compute("sum(lossqty)", ""))
                        sqlList.Add(String.Format(lossSql, Guid.NewGuid.ToString, checkID, row("facontainerid").ToString, mainLossQty, reportID, userInfo.EmployeeID, row("facurstepid").ToString))
                    End If
                    If -mainLossQty >= Val(row("faqty").ToString) Then '主卡报废完毕
                        mainStatus = 2
                    End If
                    sqlList.Add(String.Format("UPDATE container c SET c.qty = c.qty +({0}),c.status={1} WHERE c.containerid = '{2}'", mainLossQty, mainStatus, row("facontainerid").ToString))
                    sqlList.Add(String.Format("UPDATE containercurrentinfo c SET c.qty = c.qty +({0}),c.containerstatus={1} WHERE c.containerid = '{2}'", mainLossQty, mainStatus, row("facontainerid").ToString))

                End If

                Dim autoReportSql2 = String.Format(autoReportSql, reportID, row("facontainerid").ToString, row("facurstepid").ToString, sonConReportEmpDt.Rows(0)("workerid").ToString, sonConReportDt.Compute("sum(reportqty)", "").ToString,
                                              sonConReportDt.Compute("sum(SelfCheckQualifiedQty)", "").ToString, sonConReportDt.Compute("sum(SelfCheckOutOfToleRanceQty)", "").ToString, sonConReportDt.Compute("sum(SelfCheckLossQty)", "").ToString,
                                              sonConReportDt.Compute("sum(LossQty)", "").ToString, userInfo.FactoryID)

                Dim autoCheckSql2 = String.Format(autoCheckSql, checkID, reportID, row("facontainerid").ToString, row("facurstepid").ToString, userInfo.EmployeeID, sonConCheckDt.Compute("sum(checkqty)", "").ToString,
                                             sonConCheckDt.Compute("sum(acceptanceqty)", "").ToString, sonConCheckDt.Compute("sum(acceptanceqty2)", "").ToString, sonConCheckDt.Compute("sum(defectqty)", "").ToString,
                                             sonConCheckDt.Compute("sum(reworkqty)", "").ToString, sonConCheckDt.Compute("sum(lossqty)", "").ToString, userInfo.FactoryID)

                If row("facontainertype").ToString = "7" Then
                    autoReportSql2 = String.Format(autoReportSql, reportID, row("facontainerid").ToString, row("facurstepid").ToString, sonConReportEmpDt.Rows(0)("workerid").ToString, 1,
                                              0, 0, 0, -mainLossQty, userInfo.FactoryID)

                    autoCheckSql2 = String.Format(autoCheckSql, checkID, reportID, row("facontainerid").ToString, row("facurstepid").ToString, userInfo.EmployeeID, 1,
                                                 1 + mainLossQty, 1 + mainLossQty, 0, 0, -mainLossQty, userInfo.FactoryID)
                End If

                sqlList.Add(autoReportSql2)
                sqlList.Add(autoCheckSql2)

            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
        End If

        '
    End Sub
#End Region

#Region "质量提起"
    ''' <summary>
    ''' 质量提起页面获取件号信息 add by zhangrj 2022-10-08
    ''' </summary>
    ''' <param name="productID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetQualityCollectProductInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetQualityCollectProductInfo
        Dim sql = <string>
SELECT NVL(pm.qtyrequired,0) qtyrequired,p.materialtrademark,p.description,p.productid,pb.productname
,pf.productfamilyname,pf.productfamilyid,pt.producttypename,pt.producttypeid,p.charactertype partcategory
,w.part_revid,q.unqualifiednum,q.checkno,q.unqualifieddescribe,q.isdesignbasisfeatures,q.isimportantfeatures
,q.iskeyfeatures,q.qualityhistoryrecordid,q.dutyemployeeid,q.factoryid
 FROM product p 
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid
LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid
LEFT JOIN productmateriallistitem pm ON pm.bomid = p.bomid
LEFT JOIN Workflow w ON w.workflowid = p.workflowid
LEFT JOIN qualityhistoryrecord q ON q.productitemname = pb.productname AND q.togetherflag = '{0}' AND q.mfgordername = '{1}' and q.isuse = 0
WHERE p.productid = '{2}'                      
                  </string>.Value
        sql = String.Format(sql, para("togetherFlag").ToString, para("mfgOrderName").ToString, para("productID").ToString)
        Return DatabaseHelper.Query_ReturnDatTable(sql)
    End Function


    ''' <summary>
    ''' 保存质量提起 add by zhangrj 2022-10-09
    ''' </summary>
    ''' <param name="param"></param>
    ''' <param name="user"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveQulityCollectData(ByVal param As DataRow, ByVal user As CurrentUserInfo) As uMESProcessResult Implements uLMSCheckIBusiness.SaveQulityCollectData
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String = ""
            '如果ID为空，则添加
            If String.IsNullOrWhiteSpace(param("QualityHistoryRecordID").ToString) Then
                strSql = <string>
INSERT INTO qualityhistoryrecord(QUALITYHISTORYRECORDID,CREATEEMPLOYEEID,CREATETIME,ISUSE,DEALNAME,WORKFLOWSTEPID
,MFGORDERNAME,STATUS,PRODUCTITEMNAME,CONTAINERNO,CHECKNO,PARTCATEGORY
,DUTYEMPLOYEEID,UNQUALIFIEDNUM,ORGFACTORYID,FACTORYID,UNQUALIFIEDDESCRIBE,ISKEYFEATURES,ISIMPORTANTFEATURES
,ISDESIGNBASISFEATURES,WORKCENTERID,SEQUENCE,togetherflag) VALUES(
                                   </string>.Value
                strSql += String.Format("'{0}',", Guid.NewGuid.ToString) '主ID
                strSql += String.Format("'{0}',", user.EmployeeID) '创建人
                strSql += String.Format("{0},", "sysdate") '创建日期
                strSql += "0," '是否有效
                strSql += String.Format("'{0}',", param("DealName").ToString) '处置单号
                strSql += String.Format("'{0}',", param("WORKFLOWSTEPID").ToString) '工序ID
                strSql += String.Format("'{0}',", param("MfgOrderName").ToString) '订单号
                strSql += "0," '状态（0：待审核，1：已审核）
                strSql += String.Format("'{0}',", param("ProductName").ToString) '零件名称
                strSql += String.Format("'{0}',", param("ContainerNo").ToString) '零件批次号
                strSql += String.Format("'{0}',", param("CheckNO").ToString) '检验编号
                strSql += String.Format("'{0}',", param("Partcategory").ToString) '零件类别
                strSql += String.Format("'{0}',", param("DutyEmployeeID").ToString) '责任者
                strSql += String.Format("'{0}',", param("UnqualifiedNum").ToString) '不合格品数
                strSql += String.Format("'{0}',", user.FactoryID) '提交单位
                strSql += String.Format("'{0}',", param("FactoryID").ToString) '责任单位
                strSql += String.Format("'{0}',", param("UnqualifiedDescribe").ToString) '不合格描述
                strSql += String.Format("'{0}',", param("IsKeyFeatures").ToString) '是否有关键特性超差
                strSql += String.Format("'{0}',", param("IsImportantFeatures").ToString) '是否有重要特性超差
                strSql += String.Format("'{0}',", param("IsDesignBasisFeatures").ToString) '是否有设计基准要素超差
                strSql += String.Format("'{0}',", user.WorkCenterID) '发生工区
                strSql += String.Format("'{0}',", param("Sequence").ToString) '顺序号
                strSql += String.Format("'{0}')", param("Togetherflag").ToString) '大批次
            Else '更新
                strSql = "SELECT q.status FROM qualityhistoryrecord q WHERE q.qualityhistoryrecordid = '" + param("QualityHistoryRecordID").ToString + "'"
                Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
                If dt.Rows(0)("status").ToString() = "1" Then
                    result.Result = False
                    result.Message = "质量提起已经审核，不可修改"
                    Return result
                    Exit Function
                End If
                strSql = <string>
UPDATE qualityhistoryrecord q SET q.unqualifiednum = '{0}',q.unqualifieddescribe = '{1}',q.checkno='{2}',
q.isdesignbasisfeatures = '{3}',q.iskeyfeatures='{4}',q.isimportantfeatures='{5}' 
WHERE q.qualityhistoryrecordid = '{6}'
                         </string>.Value
                strSql = String.Format(strSql, param("UnqualifiedNum").ToString,
                                       param("UnqualifiedDescribe").ToString,
                                       param("CheckNO").ToString,
                                       param("IsDesignBasisFeatures").ToString,
                                       param("IsKeyFeatures").ToString,
                                       param("IsImportantFeatures").ToString,
                                       param("QualityHistoryRecordID").ToString)
            End If

            If DatabaseHelper.ExecuteSql(strSql) > 0 Then
                result.Result = True
                result.Message = "保存成功"
            Else
                result.Result = False
                result.Message = "保存失败"
            End If
            Return result
        Catch ex As Exception
            result.Result = False
            result.Message = "保存出错，原因：" + ex.Message
            Return result
        End Try
    End Function

    ''' <summary>
    ''' 获取质量提起审核列表 add by zhangrj 2022-10-10
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetQualityCollectExamineData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSCheckIBusiness.GetQualityCollectExamineData
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT QUALITYHISTORYRECORDID,DEALNAME,MFGORDERNAME,PRODUCTITEMNAME,CONTAINERNO,DECODE(STATUS,1,'已审核','待审核') STATUS,UNQUALIFIEDNUM,CHECKNO,
                    PARTCATEGORY,UNQUALIFIEDDESCRIBE
                    ,w.WORKFLOWSTEPNAME,e2.FULLNAME DUTYNAME,f.FACTORYNAME,e.FULLNAME CREATENAME,
                    to_char(CREATETIME,'yyyy-MM-dd') CREATETIME,e1.FULLNAME CONFIRMNAME,to_char(CONFIRMDATE,'yyyy-MM-dd') CONFIRMDATE
                    FROM QUALITYHISTORYRECORD q
                    LEFT JOIN EMPLOYEE e ON e.employeeid=q.CREATEEMPLOYEEID
                    LEFT JOIN EMPLOYEE e1 ON  e1.employeeid=q.CONFIRMEMPLOYEEID
                    LEFT JOIN EMPLOYEE e2 ON  e2.employeeid=q.DUTYEMPLOYEEID
                    LEFT JOIN FACTORY f ON f.FACTORYID =q.FACTORYID
                    LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =q.WORKFLOWSTEPID
                    WHERE q.isuse = 0
                      </string>)
        If para.ContainsKey("factoryID") Then
            If Not String.IsNullOrWhiteSpace(para("factoryID")) Then
                sb.AppendFormat(" AND q.FACTORYID = '{0}'", para("factoryID")) '责任分厂
            End If
        End If
        If para.ContainsKey("dealname") Then
            If Not String.IsNullOrWhiteSpace(para("dealname")) Then
                sb.AppendFormat(" AND q.DEALNAME LIKE '%{0}%'", para("dealname")) '处置单号
            End If
        End If
        If para.ContainsKey("mfgordername") Then
            If Not String.IsNullOrWhiteSpace(para("mfgordername")) Then
                sb.AppendFormat(" AND q.MFGORDERNAME LIKE '%{0}%'", para("mfgordername")) '订单号
            End If
        End If
        If para.ContainsKey("productname") Then
            If Not String.IsNullOrWhiteSpace(para("productname")) Then
                sb.AppendFormat(" AND q.PRODUCTITEMNAME LIKE '%{0}%'", para("productname")) '件号
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" AND to_char(q.CREATETIME,'yyyy-MM-dd')>='{0}'", para("startedate")) '起始日期
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" AND to_char(q.CREATETIME,'yyyy-MM-dd')<='{0}'", para("enddate")) '结束日期
            End If
        End If

        If para.ContainsKey("status") Then
            If Not String.IsNullOrWhiteSpace(para("status")) Then
                sb.AppendFormat(" AND q.status={0}", para("status")) '审核状态
            End If
        End If

        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 保存质量提起审核数据 add by zhangrj 2022-10-10
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveQualityCollectExamineData(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSCheckIBusiness.SaveQualityCollectExamineData
        Dim strSql = "UPDATE qualityhistoryrecord q SET q.confirmemployeeid = '{0}',q.confirmdate = sysdate,q.status = 1 WHERE q.qualityhistoryrecordid in ({1})"
        strSql = String.Format(strSql, para("confirmEmployeeID"), para("qualityIDs"))

        If DatabaseHelper.ExecuteSql(strSql) > 0 Then
            Return True
        Else
            Return False
        End If

    End Function
#End Region

    ''' <summary>
    ''' Modify by Yuxj20180329 获取发动机所有已经派工但没有完工的任务单
    ''' 非故检工艺根据派工判断，故检工艺根据工艺类型assemblytype判断
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNotCheckContainer(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSCheckIBusiness.GetNotCheckContainer
        Dim sb As New StringBuilder
        Dim sql As String =
            <string>
                SELECT ep.workflowid,
                       me.productfamilyname,
                       ep.machineno machine,
                       ep.mfgordername,
                       ep.containerno togetherflag,
                       ep.ContainerName,
                       ep.productname,
                       ep.productid,
                       ep.productdesc description,
                       ep.plannedstartdate plannedstartdate,
                       ep.plannedfinishdate plannedcompletiondate,
                       ep.concontainerid containerid,
                       ep.workflowstepname StepName,
                       ep.workflowname || ':' || wf.workflowrevision workflownamever,
                       ep.workflowdesc wfdesc,
                       p.MATERIALTRADEMARK,
                       p.PRODUCTREVISION PART_REVID --add by longb 2022-06-10
                  FROM enginepartplan ep 
                  left join mfg_engine_data me on me.mfgorderid=ep.mfgorderid  
                  LEFT JOIN product p ON ep.productid = p.productid  
                  LEFT JOIN workflow wf ON wf.workflowid = ep.workflowid 
                  Inner JOIN (select distinct csd.containerid  from containerstepdispatch csd 
                                left join team t on t.teamid=csd.teamid 
                                left join containercheckinfo cch 
                                    on cch.containerid = csd.containerid  and csd.workflowstepid = cch.workflowstepid
                                    and nvl(csd.stepinfoid, '0') = nvl(cch.stepinfoid, '0') and cch.isuse = 0
                                where csd.Factoryid = '{0}' and
                                cch.CONTAINERCHECKINFOID is null and cch.checkerid is null and csd.isuse = 0 and instr(t.teamname,'故检'){1}0 ) csd
                  on csd.containerid=ep.concontainerid         
                  WHERE ep.concontainerid>'0' AND ep.usecompleted = '0'                                                        
            </string>
        If para.ContainsKey("PartType") Then '故检
            sb.AppendFormat(sql, para("FactoryID"), ">")
        Else '普通检验
            sb.AppendFormat(sql, para("FactoryID"), "=")
        End If
        If para.ContainsKey("TaskType") Then
            If para("TaskType") = "施工单任务" Then
                sb.AppendLine(" and ep.type in (2,3) ")
            ElseIf para("TaskType") = "订单任务" Then
                sb.AppendLine(" and ep.type=1 ")
            End If
        End If

        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and ep.mfgordername like '%{0}%' ", para("MfgOrderName"))
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat(" and ep.containerno like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and ep.machineno like '%{0}%' ", para("MachineStart"))
        End If

        If para.ContainsKey("ProductName") Then
            sb.AppendFormat(" and ep.ProductName like '%{0}%' ", para("ProductName"))
        End If

        sb.AppendLine(" Order by ep.machineno,ep.mfgordername,ep.containerno,ep.containername asc ")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 验证工序是否存在未录入实测值的检验 add by zhangrj 2023-04-20
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsInputCheckPointResult(ByVal dt As DataTable) As Boolean Implements uLMSCheckIBusiness.IsInputCheckPointResult
        Dim result = True
        Dim strSql = <string>
            SELECT sp.checkerid FROM stepcheckpoint s 
            LEFT JOIN stepcheckpointresult sp ON sp.stepcheckpointid = s.stepcheckpointid AND sp.containerid = '{1}' 
            LEFT JOIN workflowstep ws ON ws.workflowstepid = s.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec sc ON sc.specid = NVL(sb.revofrcdid,ws.specid)
            WHERE s.workflowstepid = '{0}' AND (s.qualitycheck = 1  OR (sc.iskey = 1 AND s.keycheck = 1))
                     </string>.Value
        For Each dr As DataRow In dt.Rows
            Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dr("workflowstepId").ToString(), dr("containerId").ToString()))
            '如果为空，则表示工序不存在检测项，则跳过
            If dtRes.Rows.Count = 0 Then
                Continue For
            End If
            '如果存在未录入的数据，则返回否
            If dtRes.Select("checkerid is null").Length > 0 Then
                result = False
                Exit For
            End If
        Next
        Return result
    End Function

#Region "检验验证特殊过程工序"
    ''' <summary>
    ''' 验证特殊过程工序（检验时验证）add by zhangrj 20250331
    ''' </summary>
    ''' <param name="dtDatas">需要验证的数据源</param>
    ''' <param name="userInfo">操作人员</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidSpecialCourseSpecReportByCheck(ByVal dtDatas As DataTable, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO Implements IBusiness.uLMSCheckIBusiness.ValidSpecialCourseSpecReportByCheck
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = <string>
                SELECT r.resourcename,c.containername,ws.workflowstepname,ws.workflowstepid
                FROM containerstepworkreport csw
                LEFT JOIN container c ON c.containerid = csw.containerid 
                LEFT JOIN resourcedef r ON r.resourceid = csw.resourceid
                LEFT JOIN workflowstep ws ON ws.workflowstepid = csw.workflowstepid
                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                LEFT JOIN spec s ON s.specid=nvl(sb.revofrcdid,ws.specid)
                WHERE csw.containerstepworkreportid = '{0}' AND csw.isuse = 0 AND s.isspotcheck = 1  
                AND NOT EXISTS(SELECT 1 FROM specialreportunlimitedinfo sr WHERE sr.containerid = csw.containerid AND sr.workflowstepid = csw.workflowstepid AND sr.status = 0) --未添加到无限制名单中</string>.Value '查询SQL
        Dim dtLastVData As New DataTable '最终验证的数据源
        If dtDatas.Columns.Contains("containerstepworkreportid") Then
            dtLastVData = dtDatas.Copy()
        Else
            dtLastVData = GetTransformDataByReport(dtDatas) '如果数据源不包含报工ID，则需要转换（检验页面一般没有报工ID，则需要查出报工ID）
        End If
        For Each dr As DataRow In dtLastVData.Rows
            Dim dtSpecial As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dr("containerstepworkreportid").ToString()))
            '如果为空，则表示该工序不是特殊过程工序或者报工已撤销或者是无限制工序，则不验证
            If dtSpecial.Rows.Count = 0 Then
                Continue For
            End If
            Dim drRes = dtSpecial.Rows(0) '取第一条数据即可
            '开始验证该工序报工是否有效
            reportDto = IsValidSpecSpecialReport(drRes, userInfo)
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断工序是否有效的特殊过程
    ''' </summary>
    ''' <param name="drSpec"></param>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsValidSpecSpecialReport(ByVal drSpec As DataRow, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = String.Format(<string>
                SELECT ssr.indate,'' period,ssr.resourcenames,ssr.employeenames,ssr.specialcoursespecreportno
                FROM SpecialCourseSpecMapp scs 
                LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid
                WHERE scs.workflowstepid = '{0}'</string>.Value, drSpec("workflowstepid").ToString()) '查询SQL
        Dim dtReport As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql) '报告表
        If dtReport.Rows.Count = 0 Then
            reportDto.IsValid = False
            reportDto.Message = String.Format("批次：{0}的工序：{1} 没有特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
            Return reportDto
        End If
        '
        '****开始循环判断每一个报告的有效性(每一个报告都必须有效)
        '
        For Each drRe As DataRow In dtReport.Rows
            ''查询每个报告的数据，每个报告号可能是多个报告号连起来的（每一个都要验证）
            Dim nos As String() = drRe("specialcoursespecreportno").ToString().Split(",")
            strSql = <string>
                SELECT ssb.indate,ssb.period,ssc.resourcenames,ssc.employeenames
                FROM  specialcoursespecreportbank ssb
                LEFT JOIN specialcoursespeccontent ssc ON ssc.specialcoursespecreportbankid = ssb.specialcoursespecreportbankid
                {0}
                                   </string>.Value
            Dim dtNewReport As New DataTable '最终要验证的数据
            For Each no As String In nos
                Dim sqlWhere = String.Format("WHERE ssc.code LIKE '{0}%'", no) '查询条件
                Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                If dtRes.Rows.Count = 0 Then
                    sqlWhere = String.Format("WHERE ssb.specialcoursespecreportno LIKE '{0}%'", no) '查询条件
                    dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                End If
                dtNewReport.Merge(dtRes)
            Next
            '
            '****开始做验证*********
            '
            If dtNewReport.Rows.Count > 0 Then '有新的报告数据，则验证该数据
                reportDto = IsValidSpecialReport(drSpec, dtNewReport, userInfo)
            Else
                '如果不存在新的报告数据，则验证自身即可
                Dim dtCopy = dtReport.Clone()
                dtCopy.Rows.Add(drRe.ItemArray)
                reportDto = IsValidSpecialReport(drSpec, dtCopy, userInfo)
            End If
            '如果无效了，则跳出外层循环
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断该工序的该报告是否有效
    ''' </summary>
    ''' <param name="drSpec">工序</param>
    ''' <param name="dtReport">报告列表</param>
    ''' <param name="userInfo">当前用户</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsValidSpecialReport(ByVal drSpec As DataRow, ByVal dtReport As DataTable, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        For Each drReport As DataRow In dtReport.Rows
            '判断是否没有有效期，如果两个表的有效期都是空，则提示
            If String.IsNullOrWhiteSpace(drReport("indate").ToString) Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在没有有效期的特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            '-----获取有效期
            Dim inDate As Date = Date.Parse(drReport("indate").ToString) '有效期
            If Not String.IsNullOrWhiteSpace(drReport("period").ToString) Then '如果存在年数不为空，则要将有效期累加年数
                Dim period = 0 '年数
                Integer.TryParse(drReport("period").ToString.TrimEnd("年"), period)
                inDate = inDate.AddYears(period)
            End If
            '判断有效期是否过期
            If inDate < DateTime.Now Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在已过期的特殊过程报告，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            If inDate < Date.Now.AddDays(95) Then '如果有效期低于95天过期（需给出提示，验证通过）
                reportDto.IsUnder95Day = True
                reportDto.Message = String.Format("批次：{0}的工序：{1} 有效期已低于95天后过期，请知悉。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
            End If
            '判断是否包含检验人
            If Not drReport("employeenames").ToString.Contains(userInfo.EmployeeName.Substring(0, 8)) Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的检验人员，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), userInfo.EmployeeName.Substring(0, 8))
                reportDto.IsValid = False
                Exit For
            End If
            ''判断是否包含设备
            'If Not drReport("resourcenames").ToString.Contains(drSpec("resourcename").ToString) Then
            '    reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的设备，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), drSpec("resourcename").ToString)
            '    isValid = False
            '    Exit For
            'End If
        Next
        Return reportDto
    End Function

#End Region

#Region "验证三定卡片工序 add by zhangrj 20251011"
    ''' <summary>
    ''' 验证三定卡片工序（验证主方法，入口）add by zhangrj 20251011
    ''' </summary>
    ''' <param name="dtDatas">需要验证的数据源</param>
    ''' <param name="userInfo">操作人员</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidSpecThreeFixedCardByCheck(ByVal dtDatas As DataTable, ByVal userInfo As CurrentUserInfo) As Tuple(Of Boolean, String) Implements uLMSCheckIBusiness.ValidSpecThreeFixedCardByCheck
        Dim res As New Tuple(Of Boolean, String)(True, "")
        Dim strSql As String = <string>      
                SELECT ws.workflowstepname,tcd.code,tcd.name
                FROM containerstepworkreport csw
                LEFT JOIN workflowstep ws ON ws.workflowstepid = csw.workflowstepid
                LEFT JOIN Workflow w ON w.workflowid = ws.workflowid
                LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                LEFT JOIN spec s ON s.specid=nvl(sb.revofrcdid,ws.specid)
                INNER JOIN ThreeFixedCards tc ON tc.workflowname = wb.workflowname AND tc.workflowversion = w.workflowrevision AND tc.specno = s.specno
                LEFT JOIN ThreeFixedCardDetail tcd ON tcd.parentid = tc.id AND tcd.threetype = '1' --只筛选检验员
                WHERE csw.containerstepworkreportid = '{0}' AND csw.isuse = 0
                     </string>.Value
        Dim dtLastVData As New DataTable '最终验证的数据源
        If dtDatas.Columns.Contains("containerstepworkreportid") Then
            dtLastVData = dtDatas.Copy()
        Else
            dtLastVData = GetTransformDataByReport(dtDatas) '如果数据源不包含报工ID，则需要转换（检验页面一般没有报工ID，则需要查出报工ID）
        End If
        '循环每一个数据进行验证
        For Each dr As DataRow In dtLastVData.Rows
            Dim dtThreeCard As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dr("containerstepworkreportid").ToString()))
            '如果为空，则表示该报工工序不是三定卡片，则不验证
            If dtThreeCard.Rows.Count = 0 Then
                Continue For
            End If
            If Not IsValidSpecThreeFixedCard(dtThreeCard, userInfo.EmployeeName.Substring(0, 8)) Then
                res = New Tuple(Of Boolean, String)(False, String.Format("工序：{0}是三定卡片，未包含编号为：{1}的检验人，无法检验！", dtThreeCard.Rows(0)("workflowstepname").ToString(), userInfo.EmployeeName))
                Exit For
            End If
        Next
        Return res
    End Function

    ''' <summary>
    ''' 验证三定数据是否包含该检验人
    ''' </summary>
    ''' <param name="dtThreeCard"></param>
    ''' <param name="employeeName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsValidSpecThreeFixedCard(ByVal dtThreeCard As DataTable, ByVal employeeName As String) As Boolean
        Dim res = False
        '循环每一个数据进行验证
        For Each dr As DataRow In dtThreeCard.Rows
            If dr("code").ToString() = employeeName Then
                res = True
                Exit For
            End If
        Next
        Return res
    End Function
#End Region

    ''' <summary>
    ''' 获取转换后的报工数据（特殊过程、三定卡片验证时的通用方法） 
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetTransformDataByReport(ByVal dt As DataTable)
        Dim dtNew As New DataTable
        Dim sql = <string>
                      SELECT DISTINCT csw.containerstepworkreportid FROM containerstepworkreport csw
                      WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0
                      AND NOT EXISTS(SELECT 1 FROM containercheckinfo cci WHERE cci.containerstepworkreportid = csw.containerstepworkreportid AND cci.isuse = 0) 
                  </string>.Value
        For Each dr As DataRow In dt.Rows
            Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(sql, dr("containerid").ToString(), dr("workflowstepid").ToString()))
            dtNew.Merge(dtRes)
        Next
        Return dtNew
    End Function
End Class
